正規表現で一致するセルの文字に色を付ける

あいまい検索を行うには正規表現を使う

セルの文字列の中で、指定した文字があれば色を付けるマクロを以下で紹介しています。

上のリンク先のページではセルの文字列の中から指定文字列があるかどうかをInStr関数で行っていますが、このページではあいまいな検索を行う場合にも対応できるように、正規表現で検索する方法を紹介します。

例えば以下のような住所の一覧がセルに入力されているとします。

福岡県福岡市南区
福岡県福岡市東区
福岡県北九州市小倉北区
福岡県北九州市小倉南区
福岡県柳川市
福岡県粕屋郡新宮町
佐賀県鳥栖市
佐賀県吉野ヶ里町

このときの色付けの条件は、“○○県”で始まり”町”で終わる文字列を赤色、“○○県”で始まり”市”で終わる文字列を青色にします。これらの条件に合う文字列を正規表現を使わずに検索させるには、全ての県と町と市の組み合わせを検索条件として準備しなければなりませんが、正規表現を使えばあいまい検索が可能になります。

変更前と変更後で以下のようにしたい場合です。

正規表現を利用するための事前設定

正規表現を利用するにはVBScriptのRegExpクラスを利用します。

以下の手順で参照設定を追加することで利用できるようになります。

VBAの画面でツールメニュー→参照設定　を選択します。
参照設定ダイアログで「Microsoft VBScript Regular Expressions 5.5」にチェックを付けます。
OKボタンを押して閉じます。

正規表現で一致したセル文字に色を付ける関数

以下の関数は、セルの文字列の中から正規表現を使って文字列を検索し、一致した箇所に指定した文字色を付ける関数です。

引数として３つ持たせており、先頭から順に、「セル選択範囲のRangeオブジェクト」「検索する文字列の正規表現」「一致した文字に付ける色」になります。

Sub SetPartColorRegExp(a_rTarget As Range, a_sSearch, a_lSetColor)
    Dim reg         As New RegExp       '// 正規表現クラス
    Dim oMatches    As MatchCollection  '// 正規表現に一致した結果コレクション
    Dim oMatch      As Match            '// 結果コレクションの１単位
    Dim iLen                            '// 検索一致文字列長
    Dim r           As Range            '// 選択セル範囲の処理中の１セル
    Dim iPosition                       '// セルの文字列で検索に一致した先頭位置
    Dim i                               '// ループカウンタ
    
    '// 検索文字列
    iLen = Len(a_sSearch)
    If iLen = 0 Then
        Exit Sub
    End If
    
    '// 正規表現の条件設定
    reg.Global = True           '// 文字列の最後まで検索（True：する、False：しない）
    reg.IgnoreCase = False      '// 大文字小文字の区別（True：しない、False：する）
    reg.Pattern = a_sSearch     '// 検索する正規表現条件
    
    '// セル範囲を１セルずつループ
    For Each r In a_rTarget
        iPosition = 0
        
        '// セル文字列から正規表現での検索を行う
        Set oMatches = reg.Execute(r.Value)
        
        '// 検索で見つかった個所の数をループ
        For i = 0 To oMatches.Count - 1
            '// 見つかった個所を取得
            Set oMatch = oMatches.Item(i)
            
            '// 検索一致の先頭位置を取得
            iPosition = oMatch.FirstIndex
            
            '// 検索一致文字列長を取得
            iLen = oMatch.Length
            
            '// 検索一致部分に着色
            r.Characters(Start:=iPosition + 1, Length:=iLen).Font.Color = a_lSetColor
        Next
    Next
End Sub

Sub SetPartColorRegExp(a_rTarget As Range, a_sSearch, a_lSetColor)

Dim reg As New RegExp '// 正規表現クラス

Dim oMatches As MatchCollection '// 正規表現に一致した結果コレクション

Dim oMatch As Match '// 結果コレクションの１単位

Dim iLen '// 検索一致文字列長

Dim r As Range '// 選択セル範囲の処理中の１セル

Dim iPosition '// セルの文字列で検索に一致した先頭位置

Dim i '// ループカウンタ

'// 検索文字列

iLen = Len(a_sSearch)

If iLen = 0 Then

Exit Sub

End If

'// 正規表現の条件設定

reg.Global = True '// 文字列の最後まで検索（True：する、False：しない）

reg.IgnoreCase = False '// 大文字小文字の区別（True：しない、False：する）

reg.Pattern = a_sSearch '// 検索する正規表現条件

'// セル範囲を１セルずつループ

For Each r In a_rTarget

iPosition = 0

'// セル文字列から正規表現での検索を行う

Set oMatches = reg.Execute(r.Value)

'// 検索で見つかった個所の数をループ

For i = 0 To oMatches.Count - 1

'// 見つかった個所を取得

Set oMatch = oMatches.Item(i)

'// 検索一致の先頭位置を取得

iPosition = oMatch.FirstIndex

'// 検索一致文字列長を取得

iLen = oMatch.Length

'// 検索一致部分に着色

r.Characters(Start:=iPosition + 1, Length:=iLen).Font.Color = a_lSetColor

End Sub

処理自体はコメントの通りですが、RegExpクラスの使い方にはルールがあり、「1. 条件設定を行い」、「2. Executeメソッドを実行し」、「3. MatchCollectionの中から一致箇所を抽出する」、という流れに従う必要があります。

セルの一部の文字色を変更するのはRangeオブジェクトのCharactersプロパティです。

Charactersプロパティの詳細については「セルの文字列を１文字単位で装飾する」をご参照ください。

使い方

上のSetPartColorRegExp関数の使い方を説明します。

引数の３つの「セル選択範囲のRangeオブジェクト」「検索する文字列の正規表現」「一致した文字に付ける色」を設定することで利用できます。

３番目の引数にはRGB関数を使っていますが、Font.Colorプロパティに設定可能なLong型の値やVBAの色定数でも構いません。

なお、色については「VBAでWebカラーCSS3の147色を指定する」で紹介しています。

Sub SetPartColorRegExpTest()
    '// ○○県～町
    Call SetPartColorRegExp(Selection, ".*県.*町", RGB(255, 0, 0))
    
    '// ○○県～市
    Call SetPartColorRegExp(Selection, ".*県.*市", vbBlue)
End Sub

Sub SetPartColorRegExpTest()

'// ○○県～町

Call SetPartColorRegExp(Selection, ".*県.*町", RGB(255, 0, 0))

'// ○○県～市

Call SetPartColorRegExp(Selection, ".*県.*市", vbBlue)

End Sub

実行後は、上で紹介した変更後の状態になります。

注意点

SetPartColorRegExp関数を使う際の注意点があります。

それは色を付ける順番はSetPartColorRegExp関数の呼び出し順に従う点です。

例えば以下のように”○○県”から”区”までを緑色にしたい条件を追加するとします。

    '// ○○県～区
    Call SetPartColorRegExp(Selection, ".*県.*区", RGB(0, 255, 0))

1 2	'// ○○県～区 Call SetPartColorRegExp(Selection, ".県.区", RGB(0, 255, 0))

このコードを追加する位置によって、結果が変わります。先に「県～区」を緑にして、そのあとに「県～市」を青にすると、左側の絵のように上書きされたようになり、逆であれば右側の絵のようになります。

県～市までの青色を生かすのか、それとも、県～区までの緑を生かすのか、などの重複部分があると、SetPartColorRegExp関数の呼び出し順が後になった方が採用されることになります。

色を付ける条件に一致する文字列が重複している場合は、SetPartColorRegExp関数の呼び出し順序の考慮が必要になります。

あいまい検索を行うには正規表現を使う

正規表現を利用するための事前設定

正規表現で一致したセル文字に色を付ける関数

使い方

注意点

関連記事