エクセルで連続検索しセルに色をつける。

質問

エクセルでShieet1Ｊ列10からＢＢ10000までのセルに4桁の数値（重複あり）がランダムにあります。
この4桁の数値をShieet2のＡ列1からＡ列100に入力した数値から検索してセルに色をつけたいのですが
良い方法はありませんでしょうか。
現在はエクセルの置換え機能を使ってますが毎日200くらいの作業があり、困っております。

　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　宜しくお願いいたします。

回答No.2

mitarashi · ベストアンサー

いまいち要件がはっきりしません。
Sheet2!A1の値を、Sheet1!J10:BB10000の中で検索し、ヒットしたら、当該セルに着色する。
次いで、Sheet2!A2以降についても同様に検索し、ヒットしたら着色する
という方向でしょうか？
Sheet2!A1以降行が変わる毎に、色は変えるのでしょうか。もっとも、100色も使い分けても見分けられないと思いますが。
上記方向と仮定して、Sheet2!A1一個分だけ書いてみました。
処理は一秒たらずで済みますが、25%ズーム表示でも相当スクロールしないといけない範囲から探すのは嫌になりますね。
着色してから、何がなさりたいのでしょうか。

Sub test()
Dim targetRange As Range
Dim buf As Variant
Dim i As Long, j As Long, myColorIndex As Long
Dim myKey As Variant

Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
Set targetRange = Worksheets("Sheet1").Range("J10:BB10000")
buf = targetRange
myColorIndex = 4
myKey = Worksheets("Sheet2").Range("A1").Value
With targetRange
For i = 1 To UBound(buf, 1)
For j = 1 To UBound(buf, 2)
If buf(i, j) = myKey Then .Cells(i, j).Interior.ColorIndex = myColorIndex
Next j
Next i
End With
Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic
End Sub

試験データ作成
Sub makeSampleData()
'試験データ作成
With Range("J10:BB10000")
.Clear
.Formula = "=int(rand()*10000)"
.Value = .Value
End With
End Sub

お礼

ご回答ありがとうございます。
お礼がたいへん遅くなり申し訳ありませんでした。
まさに、ご回答いただきましたとおりです
ほんとうにありがとうございます。
恐縮ですがSheet2!A1をA50までできるようにしたいのですがどのようにしたらよろしいのでしょうか

回答No.5

mu2011

一例です。
条件付き書式設定は如何でしょうか。
（１）Sheet2を選択→Ctrl+Ｆ３キー押下→名前欄に任意名（仮にabc）→参照範囲欄に
　　　=Sheet1!$A$1:$A$100を入力→ＯＫ
（２）Sheet1のJ10:BB10000を選択→条件付き書式（エクセルグレード不明につき、
　　　ｗｅｂ検索して下さい）→「数式を」又は「数式を利用して～」を選択→
　　　数式欄に=COUNTIF(abc,J10)を入力→書式ボタン押下→パターンタブ又は、
　　　塗りつぶしタブで色を選択→ＯＫ

お礼

ご回答ありがとうございます。
お礼がたいへん遅くなり申し訳ありませんでした。
条件付き書式設定でこのようなことができることは知りませんでした
今回はANO2のご回答を活用させていただきますがmu2011さんのご回答も私にとっては新しい領域のきっかけになりました。

回答No.4

imogasi

セルに色を着けるという機能は、エクセルでは書式」に属する機能で実現するもの。
エクセル関数は、セルの値は見つけたリ、判別したり、加工したりできるが、「書式」については手が及ばない仕組みにしてあるのだ。
そういうエクセル関数の基本のことを知らないのではないか。
ーー
条件付き書式というのが在る。そこで数式まがいの式を使うがそれで賄えるのか？
ーー
置換操作で置換後の値を検索語（数）と同じに指定し、書式でパターン色を指定すると、セルの色だけが変わったように見える。但し１，３，５などと複数指定は出来ないかもしれない。
ただし
１の桁が１の数を問題にするような場合は
検索語　*1
置換後　指定しない
書式　セルのパターン色ーブルー
など特殊例ならできるだろう。例　１，２１、７１・・のセルに色がつく
ーー
VBAならセルの書式も（条件に該当するか判別して）自由に出来る。VBAを勉強すること。
ーー
連続操作・繰り返し操作も普通はVBAの領分。

お礼

ご回答ありがとうございます。
お礼がたいへん遅くなり申し訳ありませんでした。
ANO2mitarashiさん回答のマクロを試してみて感動しました。
マクロの勉強を試みます
ご回答ありがとうございました。

回答No.3

tom04

こんばんは！
＞Ａ列1からＡ列100に入力した数値から検索してセルに色をつけたい・・・
とありますが、Sheet2にあれば同じ色で塗りつぶせば良いのですよね？

とりあえずそういうコトだとしてのVBAでの一例です。

画面左下のSheet1のSheet見出し上で右クリック　→　コードの表示　→　VBE画面が出ますので
↓のコードをコピー＆ペーストしたマクロを実行してみてください。
（Alt+F8キー　→　マクロ　→　マクロ実行です）
※　とりあえずSheet1のA列にデータがあれば「赤」にしてみました。

Sub 色付け() 'この行から
Dim i, j As Long
Dim ws As Worksheet
Set ws = Worksheets(2)
Application.ScreenUpdating = False
Cells.Interior.ColorIndex = xlNone
For j = 10 To 54
For i = 10 To Cells(Rows.Count, j).End(xlUp).Row
If WorksheetFunction.CountIf(ws.Columns(1), Cells(i, j)) Then
Cells(i, j).Interior.ColorIndex = 3 '←「赤」にしています。
End If
Next i
Next j
Application.ScreenUpdating = True
End Sub 'この行まで

※　他の色にしたい場合は「カラーインデックス」で検索してみてください。
※　Sheet2のデータごとに色を変えたいのであれば、B列に色を付けてそれを表示させる方法もあります。
しかし、100色となると見た目になかなか判り難いでしょうし、マクロを実行しても結構な時間を要します。

参考になりますかね？m(_ _)m

お礼

ごかいとうありがとうございます。
お礼がたいへん遅くなり申し訳ありませんでした。
パソコンの問題だと思いますがマクロの処理終了まで5分くらいかかってしまいました。
パソコンに問題がないか確認してみます。
ご丁寧な回答をありがとうございました。

回答No.1

NURU_osan

マクロプログラムを組むのが一番です。

お礼

ご回答ありがとうございます。
ご指摘のとおりマクロを勉強します。