エクセルマクロVBAで、複数の特定文字列を1つでも含むセルの色を変更するプログラムを紹介します。
具体的には、以下の2つを行うVBAプログラムを紹介します。
・複数(1つでも可能)の特定文字列を含まないセル→色を付けない
なお特定の文字列が1つの場合でも対応できるプログラムを作成することで、より汎用性を持たせています。
エクセルマクロVBAで複数の特定文字列を含むセルに色を付ける
ここでは、以下のデータ一覧を事例にして複数の特定文字列(の内、1つだけでも)を含むセルに色を付けるVBAプログラムを紹介します。
このプログラムを実行すると、以下のように特定文字列を含むセルのみを色を変更します。
ここでは対象文字列を「愛知」と「手数料」の2つのキーワードの内、どちらか一方を含んでいるセルを黄色にしています
対象文字列は、VBAプログラムの中で任意のキーワードを入力できるようにします。
それでは以下でVBAプログラムを紹介していきます。
VBAプログラムの解説
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 |
'プログラム0｜変数宣言の指定 Option Explicit 'プログラム1｜プログラム開始 Sub ColorCellsWithKeywords() 'プログラム2｜キーワードを入力 Dim keywords As String keywords = InputBox("対象にしたい文字列を記入。複数ある場合は、「,」で区分けすること") 'プログラム3｜キーワードがない場合、プログラムを終了 If keywords = "" Then: Exit Sub 'プログラム4｜シート設定 Dim ws As Worksheet Set ws = ActiveSheet 'プログラム5｜対象セル範囲を設定 Dim myrange As Range Set myrange = ws.UsedRange() 'プログラム6｜シート内の全てのセルに対して処理 Dim cell As Range For Each cell In myrange Debug.Print cell.Address 'プログラム7｜1行目(ヘッダー)のセルは省略 If cell.Row = 1 Then: GoTo Continue 'プログラム8｜入力したキーワードを一つずつ処理 Dim keyword As Variant For Each keyword In Split(keywords, ",") 'プログラム9｜対象セルがキーワードを含んでいれば黄色、いなければ色をなくす If InStr(cell.Value, keyword) > 0 Then cell.Interior.ColorIndex = 6 Exit For Else cell.Interior.ColorIndex = xlNone End If Next 'プログラム10｜プログラム7のジャンプ先 Continue: Next 'プログラム11｜プログラム終了 End Sub |
以下で詳しく説明します。
プログラム0｜変数宣言の指定
|
1 |
Option Explicit |
「Option Explicit」とは、変数宣言を強制するためのものです。
予期しないエラーを防止できるため「Option Explicit」を入力することを習慣化することを推奨しています。
詳しい説明は以下のページで紹介しています。
プログラム1｜プログラム開始
|
1 |
Sub ColorCellsWithKeywords() |
VBAではプロシージャという単位でプログラムを呼び出します。
プロシージャの構文は下記となっています。
|
1 2 3 |
Sub プロシージャ名() '実行プログラム End Sub |
「Sub」で始まり「End Sub」で終わります。
プロシージャに関連するmoduleの話については以下で説明しています。
プログラム2｜キーワードを入力
|
1 2 |
Dim keywords As String keywords = InputBox("対象文字列を記入。複数ある場合は、「,」で区分けすること") |
「InputBox()」で入力用のメッセージボックスを表示させます。
ここに対象としたい文言を入力します。
対象の文言が複数ある場合は、以下のように「,」でつなぎます。
ここでは上記のようにメッセージボックスに入力します。
これで「愛知」と「手数料」の文字列を取得し、これらのうち一方の文字列を含んでいるセルに色を付けます。
メッセージボックスについては、以下のページでまとめています。
プログラム3｜キーワードがない場合、プログラムを終了
|
1 |
If keywords = "" Then: Exit Sub |
プログラム2の入力値がない場合、「Exit Sub」でプログラムを途中終了させます。
プログラム4｜シート設定
|
1 2 |
Dim ws As Worksheet Set ws = ActiveSheet |
マクロ実行時にアクティブになっているエクセルシートをwsとしてとして扱います。
プログラム5｜対象セル範囲を設定
|
1 2 |
Dim myrange As Range Set myrange = ws.UsedRange() |
アクティブなシート(ws)のセル範囲をUsedRangeで取得します。
これで対象範囲を取得できます。
ただし「対象シート.UsedRange」で取得できるセル範囲は、対象シートで[Ctrl] + [End]のショートカットキーを実行したときに選択されるセルまでが範囲となります。
この事例では、[Ctrl] + [End]のショートカットキーでF18を選択しています。
したがってmyrangeは「A1:H18」が対象範囲です。
プログラム6｜シート内の全てのセルに対して処理
|
1 2 3 4 |
Dim cell As Range For Each cell In myrange '(中略) Next |
For Each文を使って、myrange内のセルを一つずつ処理をしていきます。
「cell.Address」でセル番号を確認できるので、Debug.Printで全てのセルを処理できているかどうかを検証してみます。
Debug.Printでの検証結果
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 |
Dim cell As Range For Each cell In myrange Debug.Print cell.Address Next >>>$A$1 >>>$B$1 >>>$C$1 >>>$D$1 >>>$E$1 >>>$F$1 ・ ・ ・ 略 ・ ・ ・ >>>$A$18 >>>$B$18 >>>$C$18 >>>$D$18 >>>$E$18 >>>$F$18 |
これで対象となるセル範囲を一つずつ取得できているのが分かります。
プログラム7｜1行目(ヘッダー)のセルは省略
|
1 |
If cell.Row = 1 Then: GoTo Continue |
1行目はヘッダー情報なので処理をスキップします。
スキップ先はプログラム10です。
プログラム8｜入力したキーワードを一つずつ処理
|
1 2 3 4 |
Dim keyword As Variant For Each keyword In Split(keywords, ",") '(中略) Next |
プログラム2で入力したkeywordsを「,」で区切って、繰り返し処理を行います。
ここでは「keywords=愛知,商品売却」なので、Debug.Printで検証すると以下のようになります。
Debug.Printでの検証結果
|
1 2 3 4 5 6 |
For Each keyword In Split(keywords, ",") Debug.Print keyword Next >>>愛知 >>>手数料 |
このようにプログラム2で入力したkeywordsを「,」で区切って、繰り返し処理を行うことができます。
なお「aaa,bbb,ccc」のように3つの文字列はもちろん、「,」でつなげば大量の文字列を対象にできます。
プログラム9｜対象セルがキーワードを含んでいれば黄色、いなければ色をなくす
|
1 2 3 4 5 6 |
If InStr(cell.Value, keyword) > 0 Then cell.Interior.ColorIndex = 6 Exit For Else cell.Interior.ColorIndex = xlNone End If |
セル範囲のなかで、対象文字列を含むセルを黄色にして、含まないセルの色をなくし(xlNone)ます。
Instr関数でセル内に対象文字列を含むかどうかチェック
これで「cell(セル)に、対象文字列(keyword)が含まれていれば」という記述となります。
instr関数を使った事例は以下で紹介しています。
Interior.ColorIndexで色を付ける
Interior.ColorIndexでセルを塗りつぶすことができます。数値などを使って色を指定できます。
・セル.Interior.ColorIndex = xlNone → 色をなくす
この数値を変更することで、塗りつぶしの色を変更可能です。
たとえば、3は赤、15は灰色を指定することが可能です。
色の好みに合わせて、数値を変更すると良いです。
プログラム10｜プログラム7のジャンプ先
|
1 |
Continue: |
プログラム7で対象セル(cell)が1行目だった場合、プログラム8-プログラム9を省略します。
Forの繰り返し処理を省略する場合は以下で詳しく説明しています。
プログラム11｜プログラム終了
|
1 |
End Sub |
プログラム1と対になるプログラムで、プログラム終了させるための記述です。
「End Sub」を読み込むと、プログラムが終了します。
Excel VBAについて詳しく理解したいなら
VBAを活用すると、仕事を効率化できる幅を広げることができます。
たとえば私が実際にVBAを活用して効率化してきた作業は以下の記事で紹介しています。
興味がある人は以下の記事もご覧ください。
動画でも解説しています。
エクセルマクロVBAで出来ることを15の事例で紹介｜日常業務をラクにするヒントを見つけよう！
(音声が小さいので、ボリュームを上げてご覧いただければと思います)
VBAの勉強方法
私はプログラミング初心者からVBAを勉強を始めて少しずつレベルアップしていきました。
少しずつレベルアップしながら、難しい内容に挑戦していくと効率的に学ぶことができます。
上記のリンクでは、VBA勉強に役立つ内容を紹介しています。
興味がある人はご覧ください。