エクセルで特定の文字列の含まれるセルのある行の色を変更したいと思ってお

質問

エクセルで特定の文字列の含まれるセルのある行の色を変更したいと思っておりますが、関数では出来ないようなのでVBAで作業をしております。なかなかうまくいかずで困ってしまっております。

下記のような関数でシート一枚は出来たのですが、それ以外のシートには反映がされません。

Private Sub Worksheet_Change(ByVal Target As Range)
Dim r As Range
If Application.Intersect(Target, Range("A2:A10000")) Is Nothing Then Exit Sub
For Each r In Target
If r.Column = 1 Then
Select Case r.Value
Case "○": r.Resize(1, 12).Interior.ColorIndex = 19
Case "×": r.Resize(1, 12).Interior.ColorIndex = 3
Case "△": r.Resize(1, 12).Interior.ColorIndex = 6
Case Else: r.Resize(1, 12).Interior.ColorIndex = xlNone
End Select
End If
Next r
End Sub

無知なので、ネットで調べて上記のような数式を拾ってきたのですが、どうやら1シート分の設定に書かれているようです。。。

全シートに反映がされるように設定をするにはどこをどのように書き換えればよろしいでしょうか。
お分かりの方がいらっしゃいましたら、よろしくお願いいたします。

回答No.5

keithin · ベストアンサー

ALT+F11でVBE画面を出す
プロジェクトエクスプローラで，当該のブックのVBAProjectの中にあるThisWorkbookをＷクリックする
現れたシートに下記をコピー貼り付ける

private sub Workbook_SheetChange(byval Sh as object, byval Target as range)
　dim h as range, hx as range
　dim c

　set hx = application.intersect(target, sh.range("A:A"))
　if hx is nothing then exit sub

　for each h in hx
　　select case h.value
　　　case "○"
　　　　c = 19
　　　case "×"
　　　　c = 3
　　　case "△"
　　　　c = 6
　　　case else
　　　　c = xlnone
　　end select

　　h.resize(1, 12).interior.colorindex = c
　next
end sub

＃作業グループで操作してもエラーにならない対応
＃ループする範囲の効率化

シート１のマクロは消しておく。

お礼

分かりやすいご説明、大変ありがとうございました！
こちらのやり方で、実行が出来ました。
これからはVBAの勉強を少しずつ初めて行きたいと思っています。
助かりました！ありがとうございます。

回答No.6

imogasi

こんなのエクセルー検索・置換で出来るのではないか。
＞無知なので
ＶＢＡ云々するより、自分でエクセルの本体の機能を良く勉強するほうが肝心と思うが。
ーー
データセル範囲を範囲指定
編集ー置換ー
検索する文字列　*a*　セルの値の文字列にaガ含まれている場合
置換する文字列　なし　書式　フォントー赤
シート
行
数式
全て置換をクリック
ーー
最近のエクセルバージョンでは置換後の書式設定が出来るはず。
個の操作をマクロの記録を採れば、ＶＢＡ化できるはず。
上記は＞特定の文字列が複数ある場合は１度では出来ないが、ＶＢＡ化して直線的に場合のコードを並べれば１ぱっつで成就する。
=＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝
その行のセルを全てを色を着けるなら
操作は
例えばＡ２：Ｄ７に
xx a
- xc ad
- asd
a d f g
d f g hh
s a s d
（注ーは空白セルを示す）
セル範囲を指定し
書式ー条件付き書式ー数式が
=COUNTIF($A2:$D2,"*a*")>=1
書式設定（例えばセルパターンの色グリーン）
ＯＫ
第６行目以外はセルがグリーンになる。
ＶＢＡ化したければ、この操作をマクロの記録を採ればよい。

お礼

ご回答ありがとうございました。

そうですね、エクセルの機能をまずはもっと勉強すべきですね。おっしゃられる通りでございます。
頂きました内容、今後の参考に使わせていただきます。
ありがとうございました。

回答No.4

nattocurry

このマクロで、本当に「特定の文字列の含まれるセルのある行の色を変更」出来たのでしょうか？

Worksheet_Changeイベントは、セルが変更されたときに自動で実行されるもので、
この内容は、変更されたセルがA列の場合に、そのセルの行しか色は変わらないと思いますけど。
すべてのセルで[F2]とリターンを繰り返せば、出来ないこともありませんが。

それに、これは、「特定の文字列の含まれるセル」ではなく、「特定の文字列のセル」の処理ですよ。
部分一致ではなく、完全一致です。
それが望む仕様なら構わないのですが。

条件が○×△の3つだけなら、マクロを使わなくても、条件付き書式でできますよ。

シートの下にあるシートタブを右クリックして、「すべてのシートを選択」した状態のまま、

A2:L10000 を範囲選択し、

「書式　－　条件付き書式」で

　「数式が」「=$A1="○"」
　　　好きな書式を設定
　「数式が」「=$A1="×"」
　　　好きな書式を設定
　「数式が」「=$A1="△"」
　　　好きな書式を設定

にすれば良いです。
最初は条件が１つしか表示されないので、「追加(A)>>」ボタンで追加します。

そのあと、全シート選択を解除しないまま何かの処理をすると、全シートに反映されてしまうので、シートタブを触って、全シート選択を解除しましょう。

お礼

VBAで更新が出来たのですが、データが重たくなってしまいましたので、教えていただいた通りの条件付き書式で対応が出来ました。

教えてくださいまして、ありがとうございました。

回答No.3

MackyNo1

提示されたマクロコードは、そのシートを変更したいときに自動的に実行されるようになっています。
したがって、他のシートでも実行したい場合は、このコードをコピーし、対象のシート名部分を右クリック「コードの表示」して「貼り付け」する必要がありあます。

あるいは、このイベントマクロが設定されているシートそのものをコピーして（シート名部分をドラッグ）、データ部分を後から追加あるいはコピー貼り付けする方法のほうが簡単かもしれません。

お礼

ご回答ありがとうございました。

参考にさせていただきます。

回答No.2

qualheart

VBを「Sheet1」にしか設定してないからですね。
Visual Basic Editorで開いたとき、左側のツリーでSheet1が選択され、その中に上記コードが書かれていると思います。
Sheet1用のコードなので、Sheet1にしか適用されません。
すべてのシートに適用したいなら、ツリー上に他のシート名もあるので、それぞれに同じコードを入力する必要があります。

ただ、上記の処理はVBを使わなくても条件付き書式でもできますね。
A2:L10000を選択した状態で、書式→条件付き書式を選択し、条件１を「数式が」にして「=$A2="○"」と入力し書式としてセル色のパターンを設定すれば、○がA列に入力したときにL列までのセル色が自動的に変わるようになります。
同様に×や△も条件を追加して設定すればまったく同じことができます。

ご参考まで。

お礼

ご回答ありがとうございました。

皆様のご意見を聞いて、VBAのやり方と条件付き書式でそれぞれ実行してみました。
条件付き書式はご回答頂いたようなやり方で可能でした。
VBAにすると起動や保存に時間が結構かかってしまうのえ、条件付き書式を採用することにしました。

ご丁寧にありがとうございました。

回答No.1

merlionXX

シートのモジュールに書き込んだと思います。
それを一旦消してから、
ThisWorkbookモジュールに

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
　　Dim r As Range
　　If Application.Intersect(Target, Range("A2:A10000")) Is Nothing Then Exit Sub
　　For Each r In Target
　　　　If r.Column = 1 Then
　　　　　　Select Case r.Value
　　　　　　　　Case "○": r.Resize(1, 12).Interior.ColorIndex = 19
　　　　　　　　Case "×": r.Resize(1, 12).Interior.ColorIndex = 3
　　　　　　　　Case "△": r.Resize(1, 12).Interior.ColorIndex = 6
　　　　　　　　Case Else: r.Resize(1, 12).Interior.ColorIndex = xlNone
　　　　　　End Select
　　　　End If
　　Next r
End Sub

をコピペしてみてください。

そっくりですが、
Private Sub Worksheet_Change(ByVal Target As Range)
↓
Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)

に変わってます。

お礼

ご回答ありがとうございます。

>シートのモジュールに書き込んだと思います。

はい、その通りでした。
ThisWorkbookモジュールに記載したところ、全シートへの反映がされていました。
お手数おかけしました。ご親切にどうもありがとうございました。