エクセルマクロVBAで、複数の特定文字列を1つでも含む行の色を変更するプログラムを紹介します。
具体的には、以下の2つをこのページで説明します。
・複数(1つでも可能)の特定文字列を含む行だけ色を付けない
特定の文字列が1つの場合でも対応できるプログラムとなっています。
目次
- 1 エクセルマクロVBAで複数の特定文字列を含む行に色を付ける
- 2 VBAプログラムの解説
- 2.1 プログラム0｜変数宣言の指定
- 2.2 プログラム1｜プログラム開始
- 2.3 プログラム2｜キーワードを入力
- 2.4 プログラム3｜キーワードがない場合、プログラムを終了
- 2.5 プログラム4｜シート設定
- 2.6 プログラム5｜シートの最右列を取得
- 2.7 プログラム6｜変数設定
- 2.8 プログラム7｜2行目以降を行ごとに取得
- 2.9 プログラム8｜対象行にデータが含まれていなければプログラム11へ
- 2.10 プログラム9｜プログラム2のキーワードを全て取得
- 2.11 プログラム10｜各行にキーワードを含むセルがあれば行を黄色、なければ色をなくす
- 2.12 プログラム11｜プログラム8のジャンプ先
- 2.13 プログラム12｜プログラム終了
- 3 エクセルマクロVBAで複数の特定文字列を含まない行に色を付ける
- 4 Excel VBAについて詳しく理解したいなら
エクセルマクロ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 50 51 |
'プログラム0｜変数宣言の指定 Option Explicit 'プログラム1｜プログラム開始 Sub ColorRowsWithKeywords() 'プログラム2｜キーワードを入力 Dim keywords As String keywords = InputBox("抽出の対象となる文字列を記入。複数ある場合は、「,」で区分けすること") 'プログラム3｜キーワードがない場合、プログラムを終了 If keywords = "" Then: Exit Sub 'プログラム4｜シート設定 Dim ws As Worksheet Set ws = ActiveSheet 'プログラム5｜シートの最右列を取得 Dim col As Long col = ws.UsedRange.Columns.Count 'プログラム6｜変数設定 Dim rng As Range Dim keyword As Variant 'プログラム7｜2行目以降を行ごとに取得 Dim i As Long For i = 2 To ws.UsedRange.Rows.Count Set rng = ws.UsedRange.Rows(i) 'プログラム8｜対象行にデータが含まれていなければプログラム11へ If WorksheetFunction.CountA(rng) = 0 Then: GoTo Continue 'プログラム9｜プログラム2の全キーワードを繰り返し処理 For Each keyword In Split(keywords, ",") 'プログラム10｜各行にキーワードを含むセルがあれば行を黄色、なければ色をなくす If Not rng.Find(keyword, Lookat:=xlPart) Is Nothing Then ws.Range(Cells(i, 1), Cells(i, col)).Interior.ColorIndex = 6 Exit For Else ws.Range(Cells(i, 1), Cells(i, col)).Interior.ColorIndex = xlNone End If Next 'プログラム11｜プログラム8のジャンプ先 Continue: Next 'プログラム12｜プログラム終了 End Sub |
以下で詳しく説明します。
プログラム0｜変数宣言の指定
|
1 |
Option Explicit |
「Option Explicit」とは、変数宣言を強制するためのものです。
予期しないエラーを防止できるため「Option Explicit」を入力することを習慣化することを推奨しています。
詳しい説明は以下のページで紹介しています。
プログラム1｜プログラム開始
|
1 |
Sub GetRowsWithKeywords() |
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 col As Long col = ws.UsedRange.Columns.Count |
アクティブなシート(ws)の最右列を取得します。
これでwsの最右列を取得できます。
ただし「対象シート.UsedRange」で取得できるセル範囲は、対象シートで[Ctrl] + [End]のショートカットキーを実行したときに選択されるセルとなります。
この事例では、[Ctrl] + [End]のショートカットキーでF18を選択しています。
ここではF列にデータは入力されていませんが、UsedRangeの範囲がF列を含んでいるため、col=6(F列)となります。
Debug.Printで検証してみます。
Debug.Printでの検証結果
|
1 2 3 |
Debug.Print ws.UsedRange.Columns.Count >>>6 |
このようにUsedRangeを使うと、そのエクセルシートで使用したと認識される範囲が対象となります。
この事例では大きな影響はないため、特に問題なしとして進めています。
プログラム6｜変数設定
|
1 2 |
Dim rng As Range Dim keyword As Variant |
変数を設定しています。
変数については、以下のページで紹介しています。
プログラム7｜2行目以降を行ごとに取得
|
1 2 3 4 5 |
Dim i As Long For i = 2 To ws.UsedRange.Rows.Count Set rng = ws.UsedRange.Rows(i) '(中略) Next |
対象データのシート(ws)を行ごとに処理していきます。
上記のwsの最終行を取得できます。
プログラム5のとおり、この事例ではF18がUsedRangeの範囲です。
よってwsの最終行は18となります。
Debug.Printで検証してみます。
Debug.Printでの検証結果
|
1 2 3 |
Debug.Print ws1.UsedRange.Rows.Count >>>18 |
17,18行目は空欄ですが、VBAの処理は実行されます。
しかし大きな影響はないため、特に問題なしとして進めています。
プログラム8｜対象行にデータが含まれていなければプログラム11へ
|
1 |
If WorksheetFunction.CountA(rng) = 0 Then: GoTo Continue |
UsedRangeを使用すると、対象行が空欄の場合があります。
しかし空欄行に対しては、処理を実行する意味がありません。
そこで対象行が空欄であるかどうかを判定し、空欄であればプログラム9~プログラム10を省略させます。
対象行が空欄かどうかは、プログラム7で取得したrng(行データ)に対して、ワークシート関数のCountAを使います。
rngにデータが存在していなければ、CountA=0となります。
よって「If WorksheetFunction.CountA(rng) = 0 Then」のとき、GoTo Continueでプログラム11でジャンプさせます。
プログラム9｜プログラム2のキーワードを全て取得
|
1 2 3 |
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つの文字列はもちろん、「,」でつなげば大量の文字列を対象にできます。
プログラム10｜各行にキーワードを含むセルがあれば行を黄色、なければ色をなくす
|
1 2 3 4 5 6 |
If Not rng.Find(keyword, Lookat:=xlPart) Is Nothing Then ws.Range(Cells(i, 1), Cells(i, col)).Interior.ColorIndex = 6 Exit For Else ws.Range(Cells(i, 1), Cells(i, col)).Interior.ColorIndex = xlNone End If |
対象シート(ws)の行のなかで、キーワードを含む行を黄色にして、キーワードを含まない行は色をなくします。
Findでキーワードを含むセルが存在するかどうかチェック
プログラム7で設定したrng(各行データ)に対して、プログラム2で入力したキーワードが含まれているかどうかをチェックします。
これで「各行データ(rng)に、対象文字列(keyword)が含まれていないことがなければ」という意味です。
NotとNothingを使い、二重否定にすることで、対象文字列(keyword)が含まれている場合のみを判定することができます。
対象文字列が含まれている場合、Exit Forでプログラム9のForEachから抜ける
ここでは、対象文字列(キーワードを含むセル)が存在する場合、「Exit For」でプログラム9のループを抜けるようにしています。
プログラムの仕様上、各行で対象の文字列が1つ以上存在していれば、その行は色を変更するようにしています。
もし複数の対象の文字列を「全て」含んでいる行のみ対象としたい場合など、条件を変更したい場合は、プログラム10を書き換える必要があります。
Range(Cells(行1,列1),Cells(行2,列2)で範囲を設定
上記のプログラムで各行のセル範囲を取得します。
ここでは、i行目1列(A列)からi行目col列(ここではF列)までを取得することができます。
なお「rng.Interior.ColorIndex=3」とすると、対象行の全てのセルに色を付けてしまうため、敢えて採用していません。
Interior.ColorIndexで色を付ける
Interior.ColorIndexでセルを塗りつぶすことができます。数値などを使って色を指定できます。
・セル.Interior.ColorIndex = xlNone → 色をなくす
この数値を変更することで、塗りつぶしの色を変更可能です。
たとえば、3は赤、15は灰色を指定することが可能です。
色の好みに合わせて、数値を変更すると良いです。
プログラム11｜プログラム8のジャンプ先
|
1 |
Continue: |
プログラム8で対象行が空欄のときに、プログラム9-プログラム10を省略するためのプログラムです。
Forの繰り返し処理を省略する場合は以下で詳しく説明しています。
プログラム12｜プログラム終了
|
1 |
End Sub |
プログラム1と対になるプログラムで、プログラム終了させるための記述です。
「End Sub」を読み込むと、プログラムが終了します。
エクセルマクロ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 50 51 |
'プログラム0｜変数宣言の指定 Option Explicit 'プログラム1｜プログラム開始 Sub ColorRowsWithKeywords() 'プログラム2｜キーワードを入力 Dim keywords As String keywords = InputBox("抽出の対象となる文字列を記入。複数ある場合は、「,」で区分けすること") 'プログラム3｜キーワードがない場合、プログラムを終了 If keywords = "" Then: Exit Sub 'プログラム4｜シート設定 Dim ws As Worksheet Set ws = ActiveSheet 'プログラム5｜シートの最右列を取得 Dim col As Long col = ws.UsedRange.Columns.Count 'プログラム6｜変数設定 Dim rng As Range Dim keyword As Variant 'プログラム7｜2行目以降を行ごとに取得 Dim i As Long For i = 2 To ws.UsedRange.Rows.Count Set rng = ws.UsedRange.Rows(i) 'プログラム8｜対象行にデータが含まれていなければプログラム11へ If WorksheetFunction.CountA(rng) = 0 Then: GoTo Continue 'プログラム9｜プログラム2の全キーワードを繰り返し処理 For Each keyword In Split(keywords, ",") 'プログラム10｜各行にキーワードを含むセルがあれば行をなくす、なければ黄色を付ける If Not rng.Find(keyword, Lookat:=xlPart) Is Nothing Then ws.Range(Cells(i, 1), Cells(i, col)).Interior.ColorIndex = xlNone Exit For Else ws.Range(Cells(i, 1), Cells(i, col)).Interior.ColorIndex = 6 End If Next 'プログラム11｜プログラム8のジャンプ先 Continue: Next 'プログラム12｜プログラム終了 End Sub |
上記のプログラムは、本ページ上部で説明したプログラムとほぼ同じです。
異なるのはプログラム10です。
プログラム10｜各行にキーワードを含むセルがあれば行をなくす、なければ黄色を付ける
|
1 2 3 4 5 6 |
If Not rng.Find(keyword, Lookat:=xlPart) Is Nothing Then ws.Range(Cells(i, 1), Cells(i, col)).Interior.ColorIndex = xlNone '6→xlNone Exit For Else ws.Range(Cells(i, 1), Cells(i, col)).Interior.ColorIndex = 6 'xlNone→6 End If |
変更点は2行目と5行目の右辺がそれぞれ6→xlNone, xlNone→6としています。
全体のプログラムはすでに説明していますので、ここでは省略します。
Excel VBAについて詳しく理解したいなら
VBAを活用すると、仕事を効率化できる幅を広げることができます。
たとえば私が実際にVBAを活用して効率化してきた作業は以下の記事で紹介しています。
興味がある人は以下の記事もご覧ください。
動画でも解説しています。
エクセルマクロVBAで出来ることを15の事例で紹介｜日常業務をラクにするヒントを見つけよう！
(音声が小さいので、ボリュームを上げてご覧いただければと思います)
VBAの勉強方法
私はプログラミング初心者からVBAを勉強を始めて少しずつレベルアップしていきました。
少しずつレベルアップしながら、難しい内容に挑戦していくと効率的に学ぶことができます。
上記のリンクでは、VBA勉強に役立つ内容を紹介しています。
興味がある人はご覧ください。