2021/10/17

シート内検索結果を色付け表示




1.背景

ワークシート上のセル値を検索する場面は非常に多いと思います。手段としては Ctrl+Fで表示される「検索と置換」や、フィルターなどを使っているはずです。
しかし目的のデータ位置にダイレクトにジャンプしたり、絞り込んだ行・データしか見えなかったりするため、「データ全体の中での検索されたセルの位置付け」という見方は出来難くなってしまうのも確かだと思います。

今回は、データ全体を見渡しながら目的のデータがどれなのかを把握し易くするための道具として、「検索した結果のセルを色付け」するシステムを紹介します。なお「検索と置換」と同様に「大文字と小文字を区別」「セル内容と完全一致」「半角と全角を区別」を選択できるようにしています。

2.システム概要

このシステムはサンプルファイルをExcelにアドイン登録し、作業用シートをアクティブにした上でリボン上のボタン等からシステム起動することを想定してます。
図2-1のように、アドイン登録されたボタンをクリック①すると、システムのフォームが起動③します。フォームは「モードレス」で起動しているため、シートの操作も可能です。
なお「サンプルファイル」では、ワークシート上に起動ボタン②を配置しています。
システム起動
図2-1

検索をするには「検索範囲」を指定する必要があります。図2-2のように、まずフォーム上の検索範囲横の枠部をクリック④することで、セル範囲を入力するInputBox⑤が表示されます。InputBoxを開いたまま、検索対象のセル範囲を選択⑥します。
図2-2では、C列・D列を列選択していますが、シート上で複数セルを選択することも可能です。また、列選択をしたからと言って、列全体が検索範囲になる訳では無く、データの存在する範囲(UsedRange)と重複する範囲が検索対象範囲となります。
対象範囲を選択⑥すると、その範囲がInputBox上に表示⑦されますので、良ければOKボタン⑧をクリックします。
検索範囲の設定
図2-2

OKボタン⑧をクリックすると、図2-3のように、フォームの検索範囲枠に指定した検索範囲が表示⑨されますので、検索語を入力⑩し検索条件を選択⑪したのち、「検索実行」ボタン⑫をクリックします。すると、検索にヒットしたセルの背景色が赤く⑬なります。
検索条件入力と実行
図2-3

検索を継続する場合は、検索語⑩の変更+検索条件⑪の変更を行ったのち検索実行ボタン⑫をクリックします。また、検索範囲を変更するには検索範囲枠④(⑨)を再度クリックして、対象範囲⑥を再設定します。

検索条件⑪は3種類あります。
 ・「大文字と小文字を区別」にチェックを入れると、大文字と小文字は別の文字として検索します。例えば「Ab」で検索した場合は「ABC」はヒットしませんが「Abc」はヒットします。
 ・「完全一致」にチェックを入れると、「Ab」で検索すると「Abc」はヒットせず、「Ab」や「aB」はヒットします。
 ・「半角と全角を区別」にチェックを入れると、半角と全角を別の文字として検索します。「Ab(全て半角)」で検索すると「Ab(Aは全角)」はヒットせず、「ABC(A・Bは半角)」や「abc(a・bは半角)」はヒットします。
この3種の条件の組合せのAND条件で、絞り込むことになります。

また、検索対象はアクティブなシートのみです。フォームを起動したままでのシートの移動は可能ですが、移動すると言う事は「検索対象のシートとは別なシートに居る」ことになり、検索結果が確認できない状態になります。ですので、シート移動時はユーザーが指定した「検索範囲⑨」をクリアし、移動した先で検索範囲を再設定しない限り検索が出来ない様にしています。

検索でヒットしたセルが赤くなるのは、ユーザー指定範囲に対して「条件付き書式を作り、検索語に一致したセルの背景色を赤」に設定しているからです。また、作業用シートに「既存の条件付き書式が存在」した場合、今回システムの条件付き書式を最優先に設定しますので、図2-4右ように「ヒットしたセルは、赤の背景色」に、「ヒットしなかったセルは、既存の書式」になります。
既存の条件付き書式との関係
図2-4

「色付け取消」ボタンをクリックすると、「検索実行」前の状態に戻します。「既存の条件付き書式」が存在する場合は、それが適用される状態です。
終了する場合は「終了」ボタンをクリックするか、右上×印をクリックします。終了時は、システムが作成した条件付き書式は削除され、システム起動前の状態に戻します。

なお日付値に対しては、正しい検索は出来ません。これはExcelでは年月日を、1900年1月1日を1とする数値で表しているだけで、年月日として私たちが見えているのはExcelの書式設定によるものだからです。
例えば「2021年10月10日」というセル値があっても、実際には「44479」という数値であるため、今回システムで「10を含むセル」を検索してもヒットしません。

3.プログラムの流れ

今回システムは、フォームの内容(検索語+検索条件3種)を条件付き書式の数式として組み立て、検索範囲(ユーザー設定の範囲とUsedRangeとが重複する範囲)に対して設定することで、条件付き書式の数式がTrueになったセルを条件付き書式の書式(今回は背景色=赤色)に変えています。
プログラムの流れ
図3-1

まず、フォーム上でユーザーが入力・選択した「検索語+検索条件3種」(図3-1左)を、図3-1の中央のように数式に組み立てます。使用する関数等は「条件付き書式に使う関数等の選定」で詳細説明しますが、「EXACTやSEARCHなどの文字列比較・検索関数」と「ASC関数(半角→全角に統一)」を使って組み立てています。
事前に設定したユーザー指定の検索範囲と、データが存在する範囲(UsedRange)が重複する範囲に対して、上記で作成した数式の条件付き書式を設定します。この設定により、「ユーザーが入力・選択した検索条件」にヒットするセルが「条件付き書式の書式(背景色:赤色」になります。

4.標準モジュール(Module1)

今回システムは、「標準モジュール(Module1)」「クラスモジュール(Class1)」「フォーム(UserForm1)」にコードを作成しています。標準モジュールは、Excelアドインのボタン等から呼び出される「システム起動プロシージャ」を置いています。
クラスには、ユーザーがアクティブシートを変更した時に反応するプロシージャを置いています。マクロがあるThisWorkbookでのイベントでは無いため、WithEventsを使い全てのブックのイベントにアンテナを立てています。
標準モジュールから呼び出されたフォーム内で、条件付き書式の設定などを行います。

まず標準モジュールの宣言部(図4-1)では、2行目「Private AnotherEvent As Class1」で、モジュールレベル変数として「クラスの変数宣言」をしています。
  1. '========== ⇩(1) クラスの変数宣言 ============
  2. Public AnotherEvent As Class1
図4-1

次に、Excelアドインのボタン等から呼び出される起動プロシージャが図4-2です。ボタンに登録するマクロとしては、この「SheetSearch」を指定して下さい。
  1. '========== ⇩(2) システム起動 ============
  2. Public Sub SheetSearch()
  3.  Set AnotherEvent = New Class1
  4.  Set AnotherEvent.mySheet = Excel.Application
  5.  UserForm1.Show vbModeless
  6. End Sub
図4-2

8行目「Set AnotherEvent = New Class1」で、クラスのインスタンスを作成しています。
モジュールレベル変数宣言時に「Public AnotherEvent As New Class1」としてもOKです。その場合8行目は不要です。
9行目「Set AnotherEvent.mySheet = Excel.Application」では、8行目で生成したクラスのmySheetオブジェクトに、Excel.Applicationを設定しています。

11行目「UserForm1.Show vbModeless」で、フォームをモードレスで起動しています。モードレスですので、フォームを表示したままシート操作が可能になります。逆に操作できてしまうため、「異なるシートへの移動も可」となり、今回システムの「アクティブなシートでの検索」が崩れてしまうため、(フォーム等のあるマクロブックとは別の)操作しているブックのイベントを取得する必要があり、8~9行目で他のブックのイベントを取得するようにしています。

5.クラスモジュール(Class1)

クラスモジュールの先頭部では、WithEventsを使いイベントに対応するためのオブジェクト変数の定義をしています(図5-1の16行目)。
  1. '========== ⇩(3) オブジェクト変数の定義 ============
  2. Public WithEvents mySheet As Application
図5-1

16行目で定義をしたオブジェクト変数「mySheet」は、図4-2の9行目でExcelアプリケーション全てが対象となります。そのExcelアプリケーションの(つまりは、自分も含めて全てのExcelの)SheetActivateイベントが発生した時に呼び出されるのが図5-2です。
  1. '========== ⇩(4) シート移動した時のイベント ============
  2. Private Sub mySheet_SheetActivate(ByVal Sh As Object)
  3.  Call UserForm1.FormulaDelall
  4. End Sub
図5-2

Excel内でSheetActivateイベントが発生した時(=異なるシートに移動した直後)は、21行目「Call UserForm1.FormulaDelall」で、図6-14の「FormulaDelall」プロシージャを呼出して、システムが作成した条件付き書式を削除し、且つフォーム上の検索範囲枠のアドレスを初期状態の「ここをクリック」に書き換えます。

6.ユーザーフォーム(UserForm1)

6-1.レイアウト

検索フォームは、図6-1のようなレイアウトにしました。
フォームレイアウト
図6-1

作業の流れに沿って上から、検索範囲を設定するLabel1、検索語入力枠のTextBox1を配置し、その下側に検索条件の3つのCheckBoxを置いています。そして右下に操作実行する3つのCommandButtonを配置します。
コントロール表面文字列は、固定のものは全て配置時にCaption設定しています。

6-2.フォームモジュール

6-2-1.起動時初期設定

フォーム内で共通して使用する変数を図6-2のように宣言部で宣言しています。
  1. '========== ⇩(5) フォームレベルの共通変数の宣言 ============
  2. Dim Ruser As Range         'ユーザーが指定したセル範囲(Range userの略)
  3. Dim Rsearch As Range        '条件付き書式の適用範囲(Range searchの略)
  4. Dim IsMyFormula As Boolean     '今回システムが作った条件付き書式が存在するか否かのフラグ
図6-2

26行目「Dim Ruser As Range」は「ユーザーが指定した検索範囲」です。と言って、何がなんでもユーザーが指定した範囲全域を検索する必要はありません。ユーザーが指定した範囲内で、且つデータが存在する範囲について検索を実行すれば良いのです。
その実行範囲は、図6-6の61行目で「ユーザー指定範囲 × データが存在する範囲」の重複する範囲としています。その重複する範囲を27行目「Dim Rsearch As Range」で宣言しています。

また異なる検索をする時やシステム終了する時には、今回システムで作成した条件付き書式を削除します。そのため、作成してあるか否かの情報を保持する必要があり、そのフラグの役目を28行目「Dim IsMyFormula As Boolean」で宣言します。
この変数IsMyFormulaの情報は、変数Rsearchにも持たせることが出来そうでした。しかし、ちょっと分かり難くなりそうでしたので別なフラグ変数として起こしました。

フォーム起動時に最初に実行されるのが図6-3です。
  1. '========== ⇩(6) フォーム初期設定 ============
  2. Private Sub UserForm_Initialize()
  3.  Me.Caption = "シート内検索結果を色付け表示"
  4.  Me.Label1.BorderStyle = fmBorderStyleSingle
  5.  Me.Label1.Caption = "ここをクリック"
  6. End Sub
図6-3

32行目「Me.Caption = "シート内検索結果を色付け表示"」は、フォームのタイトル部にシステム名の文字列を設定します。
33行目「Me.Label1.BorderStyle = fmBorderStyleSingle」は、ユーザーが指定した検索範囲を表示する部分であるLabel1の枠を表示させています。枠表示が無いと、どこをクリックして良いのか分からない感じがしましたので設定しました。
そしてその検索範囲の枠内に、34行目「Me.Label1.Caption = "ここをクリック"」と明示しました。このLabel1には、ユーザーが検索範囲指定をすると「指定したセル範囲」が表示されますが、シート移動をして指定範囲がクリアされると再び「ここをクリック」に変更されます(図6-14の127行目)

6-2-2.検索対象範囲の設定

フォーム上のLabel1の部分をクリックした時に呼び出されるのが図6-4です。
  1. '========== ⇩(7) 検索範囲指定 ============
  2. Private Sub Label1_Click()
  3.  On Error Resume Next
  4.   Set Ruser = Application.InputBox("検索対象範囲を選択して下さい", Type:=8)
  5.   If Err.Number = 0 Then
  6.    Call FormulaDel
  7.    Label1.Caption = Ruser.Address
  8.   End If
  9.  On Error GoTo 0
  10. End Sub
図6-4

40行目「Set Ruser = Application.InputBox("検索対象範囲を選択して下さい", Type:=8)」では、InputBoxメソッドを表示し、ユーザーに検索範囲を指定してもらいます。InputBoxメソッドの第一引数はInputBoxに表示される文字列ですが、第二引数(名前付き引数「Type:=」で指定した引数)は、図6-5から指定します。
InputBoxメソッドの引数Typeの値
 値 説明
0数式
1数値
2文字列(テキスト)既定値
4理論値(True 又は False)
8セル参照(Rangeオブジェクト)
16#N/Aなどのエラー値
64値の配列
図6-5

今回は「Type:=8」を指定することで、セル範囲を取得します。マウス等でセル範囲を指定しても、キーボードからセル範囲を入力してもOKです。しかし、それ以外(例えば、文字列など)を入力たり、何も入力せずにOKボタンをクリックすると注意を促すコメントが表示され、InputBoxを完了することが出来ません。

得られたセル範囲は、変数Ruserに代入されます。しかし、もしInputBoxメソッドで「Cancelボタンをクリックした時は、Falseが戻る」ので、変数Ruserのデータ型Range(図6-2の26行目で宣言)に入れることが出来ずにエラーが発生します。エラーでマクロ停止しては困るため、39行目「On Error Resume Next」でスルーさせ、42行目「If Err.Number = 0 Then」でエラーが発生しているか否か(=ユーザーがCancelをクリックしたか否か)を判断しています。

変数Ruserにセル範囲が代入された時は、エラーが発生していないので43~44行目を実行します。
43行目「Call FormulaDel」では図6-13を呼び出し、今回システムが作成した「条件付き書式を削除」します。
44行目「Label1.Caption = Ruser.Address」では、ユーザーが指定したセル範囲のアドレスをLabel1に表示します。

一方、Cancelボタンをクリックした際は、40行目でFalse値を変数Ruserに代入できずに、その後何もせずプロシージャを抜けます。 ですので、既に検索範囲を指定してある状態から「再度検索範囲を指定しようとしたが、止めてCanceボタンをクリックした」時には、変数Ruserが元のセル範囲のまま残っているため、前回検索範囲に対して検索を行えることになります。

6-2-3.検索の実行

「検索実行」ボタンをクリックすると、図6-6が呼び出されます。
  1. '========== ⇩(8) 検索実行 ============
  2. Private Sub CommandButton1_Click()
  3.  Dim CKB1 As Boolean    'CheckBox1(大文字小文字を区別)の値
  4.  Dim CKB2 As Boolean    'CheckBox2(完全一致)の値
  5.  Dim CKB3 As Boolean    'CheckBox3(全角半角を区別)の値
  6.  Dim Formula As String   '条件付き書式の数式
  7.  Dim Ss As String     '検索元(search Sourceの略)
  8.  Dim Sv As String     '検索語(search Valueの略)
  9.  On Error Resume Next
  10.   With ActiveSheet
  11.    Set Rsearch = Intersect(Ruser, .UsedRange)
  12.    If Not Err.Number = 0 Then
  13.     MsgBox "検索範囲を設定後、検索開始して下さい。"
  14.     Exit Sub
  15.    ElseIf WorksheetFunction.CountBlank(.UsedRange) = .UsedRange.Count Then
  16.     MsgBox "このシートにはデータが存在しません。"
  17.     Exit Sub
  18.    ElseIf Rsearch Is Nothing Then
  19.     MsgBox "指定した範囲にはデータが存在しません。再設定して下さい。"
  20.     Exit Sub
  21.    End If
  22.   End With
  23.  On Error GoTo 0
  24.  Call FormulaDel
  25.  CKB1 = Me.CheckBox1.Value
  26.  CKB2 = Me.CheckBox2.Value
  27.  CKB3 = Me.CheckBox3.Value
  28.  If CKB3 = False Then
  29.   Ss = "Asc(" & Rsearch(1).Address(False, False) & ")"
  30.   Sv = "Asc(""" & Me.TextBox1.Value & """)"
  31.  Else
  32.   Ss = Rsearch(1).Address(False, False)
  33.   If Val(Me.TextBox1.Value) = Me.TextBox1.Value Then
  34.    Sv = Me.TextBox1.Value
  35.   Else
  36.    Sv = """" & Me.TextBox1.Value & """"
  37.   End If
  38.  End If
  39.  Select Case True
  40.   Case CKB1 = False And CKB2 = False
  41.    Formula = "=Search(" & Sv & "," & Ss & ")"
  42.   Case CKB1 = False And CKB2 = True
  43.    Formula = "=" & Ss & "=" & Sv & ""
  44.   Case CKB1 = True And CKB2 = False
  45.    Formula = "=Find(" & Sv & "," & Ss & ")"
  46.   Case CKB1 = True And CKB2 = True
  47.    Formula = "=Exact(" & Ss & "," & Sv & ")"
  48.  End Select
  49.  With Rsearch.FormatConditions.Add(Type:=xlExpression, Formula1:=Formula)
  50.   .SetFirstPriority
  51.   .Interior.Color = RGB(255, 0, 0)
  52.   .StopIfTrue = True
  53.  End With
  54.  IsMyFormula = True
  55. End Sub
図6-6

52~57行目はプロシージャ内で使用する変数宣言ですが、52~54行目の変数CKB1~CKB3は検索条件を代入し、その組み合わせで条件付き書式の数式(55行目の変数Formula)を組み立てていきます。
また検索条件によって、検索元(=条件付き書式の数式の、評価するセル)と検索語(ユーザーが入力したワード)も加工する必要があるため、56~57行目の変数Ss・変数Svを作り、数式の組み立ての一部に使っています。

6-2-3-1.検索範囲のチェック
まず、61行目で使っているIntersect関数の引数に、セル範囲以外が指定された時にはエラーが発生してしまいますので、59行目「On Error Resume Next」でエラーをスルーさせます。
61行目「Set Rsearch = Intersect(Ruser, ActiveSheet.UsedRange)」は、「ユーザーが指定した検索範囲(Ruser)」と「データが存在する範囲(UsedRange)」が重複するセル範囲を取得し、変数Rsearch(=条件付き書式を設定するセル範囲)に代入します。

但し以下の場合は、変数Rsearchにセル範囲が入りません。
 ①ユーザーが検索範囲をまだ指定していない(Ruser = Nothing)
 ②ユーザーが指定した範囲にはデータが無い
 ③アクティブシートにはデータが無い
そのため、63~72行目のIf~ElseIf文で①~③の場合を抽出し、コメントを出して検索実行を中止しています。

まず、63行目「If Not Err.Number = 0 Then」は、61行目のIntersect関数でエラーが発生した時に実行されます。エラーが発生するのは、引数であるRuserまたはUsedRangeがセル範囲では無い時です。UsedRangeは「シートが全くの未使用の場合でも、A1セル」を戻して来ますので、エラーの対象となるのは「Ruserのセル範囲設定がしていない①」時となります。
その際は64行目「MsgBox "検索範囲を設定後、検索開始して下さい。"」でコメントを出して、65行目「Exit Sub」で検索中止します。

次に66行目「ElseIf WorksheetFunction.CountBlank(ActiveSheet.UsedRange) = ActiveSheet.UsedRange.Count Then」は、「データが存在する範囲(UsedRange)内の、空白セルの数」と「データが存在する範囲のセル数」を比較しています。
双方の数が同じということは、「データが存在する範囲は、全て空白セル③」ということになりますので、67行目「MsgBox "このシートにはデータが存在しません。"」とコメントを出し、検索中止します。
なおデータを全てクリアしても、背景色や罫線を含む「書式の変更」が残っていると、UserdRangeはそのセル範囲を戻して来ます。しかし「データはクリアした空白セル」であるならば、66行目If文は成立します。セル背景色が残っているのに「データがありません」とコメントされますが、これは今回システムが「セル内の文字列を検索」することが目的のため、このような仕様にしました。
また全くの未使用のシートの場合でも、UsedRangeはA1セルを戻して来ますが、そのA1セルは空白セルのため66行目のIf文は成立します。

また、69行目「ElseIf Rsearch Is Nothing Then」は、「ユーザーが指定した検索範囲」と「データが存在する範囲」との重複する範囲が無い時です。つまり「ユーザーが指定した範囲にはデータが無い②」時であり、70行目「MsgBox "指定した範囲にはデータが存在しません。再設定して下さい。"」でコメントを出し、検索中止します。

6-2-3-2.既存の条件付き書式の削除
59~75行目の検索範囲チェックが完了すると、変数Rsearchには「データが存在するセル範囲」が代入されている事になりますので、ここから実質的な検索処理が開始します。
まずは77行目「Call FormulaDel」で図6-13を呼び出し、今回システムが作成した条件付き書式が存在するならば削除をします。
また、79行目「CKB1 = Me.CheckBox1.Value」、80行目「CKB2 = Me.CheckBox2.Value」、81行目「CKB3 = Me.CheckBox3.Value」では、フォーム上の検索条件の「ON-OFF状態」を変数CKB1~3に代入します。これは、以降での1つのコードを短くするためです。

6-2-3-3.条件付き書式に使う関数等の選定
83行目以降で条件付き書式の数式を作成するのですが、まず「検索条件」は今回3種類準備しましたので、そのON-OFFの組み合わせは図6-7のように8種類(①~⑧)となります。(レ点有りがON状態)
変数検索条件
CKB1大文字小文字を区別
CKB2完全一致
CKB3半角全角を区別
図6-7

今回システムでは、条件付き書式の数式を工夫して検索を行いますが、この3種の検索条件を同時に盛り込めるようなワークシート関数は存在しません。ですので、様々な関数を検索条件に応じて使い分けることとしました。
条件付き書式に設定する数式は「=A1="ABC"」のように「セル範囲または値(A1側)」と「値("ABC"側)」を比較するようなものを集めました。
それぞれの関数等には「大文字小文字の区別の有無」「完全一致させる必要があったり部分的であれば良かったり」等の特徴がありますので、それらも一緒に整理したのが図6-8です。

検索される側=Ss、検索語=Sv とする
No.関数などSsの型大文字小文字完全一致?全角半角ワイルドカード成立時非成立時
(A)COUNTIF(Ss,Sv)Range型区別せず完全区別する10
(B)EXACT(Ss,Sv)String型区別する完全区別する不可TRUEFALSE
(C)SS = Sv (等号で結ぶ)何でも区別せず完全区別する不可TRUEFALSE
(D)FIND(Sv,Ss)String型区別する部分区別する不可1以上#VALUE!
(E)VLOOKUP(Sv,Ss,1,FALSE)Range型区別せず完全区別するSv値#N/A
(F)MATCH(Sv,Ss,0)Range型区別せず完全区別する不可1#N/A
(G)SEARCH(Sv,Ss)String型区別せず部分区別する1以上#VALUE!
図6-8

寄り道
一部のサイトや書籍に「FIND関数は、半角文字と全角文字は区別されません。例えば、半角「A」と全角「A」は同じ文字とみなされます。」との表記があります。

あれ?と思って色々試してみたのですが、ちゃんと区別しているようですし、Microsoftサポートのページにもそのような記述は見つかりませんでした。他項目の説明文のコピペ間違いか、または「半角も全角も区別せず、1文字を1として処理する」との意味で書いているのかもしれません。

条件付き書式として書式が適用されるのは「数式がTrue」の場合です。逆に「数式がFalse」の場合は適用されません。
なお「True」以外でも「0では無い数値(小数点以下もOK)」はTrue扱いになるようですし、「False」以外では「0(数値のゼロ)」「文字列」「#N/A 、#DIV/0! などのエラー値(全てのエラー値までは調べ切れませんでした)」はFalse扱いのようです。
この適用されるされないを意識すると、図6-8に並べた関数等の「成立時」の値が重要になります。ざっと見てみると、VLOOKUPを除いて成立時の値はTrue扱いにできそうです。(VLOOKUP成立時のSv値をTrue扱いにするには、一工夫必要そうです。)

この図6-7と図6-8を重ねてみると、どの検索条件にどの関数等を使えば良いか見えてきます。但し、全角半角はどれも区別してしまうため、セル範囲側・検索語側の両方に対して「ASC関数」を使って全角変換し、「全角と半角を区別しない」状態を作り出します(JIS関数を使って半角変換にしてもOKと思います)。但しASC関数が使えるのは、セル範囲側(Ss)がString型である必要があります。

検索条件と関数等を整理したのが図6-9です。
「ASC関数」を使うことを前提に考えれば、図6-9の赤丸を使う事で「3種の検索条件が網羅」出来ることになります。
No.関数等
(A)COUNTIF(Ss,Sv)
(B)EXACT(Ss,Sv)
(C)SS = Sv (等号)
(D)FIND(Sv,Ss)
(E)VLOOKUP(Sv,Ss,1,FALSE)
(F)MATCH(Sv,Ss,0)
(G)SEARCH(Sv,Ss)
図6-9

6-2-3-4.全角半角区別ON-OFFに対する処理
まず83~95行目では「全角半角区別」のチェック有無により、セル範囲側・検索語側へのASC関数の加工有無を分岐します。
83行目「If CKB3 = False Then」では「全角半角区別にチェック無し」の場合に84~85行目を実行し、「セル範囲側・検索語側の双方にASC関数を付ける」ことで「全角半角区別しない状態」にしています。
数式の形としては「=A1="ABC"」のようにしますので、「A1」側を変数Ssとし「"ABC"」側を変数Svとします。

84行目「Ss = "Asc(" & Rsearch(1).Address(False, False) & ")"」では、Ss側を作成しています。条件付き書式の数式は、適用先範囲の左上角のセルに対する数式としますので、貼り付けセル範囲である変数Rsearchの1番目(=左上角)のセル「Rsearch(1)」のアドレスを指定します。且つアドレスは行・列固定とはしませんので「$マーク無し」とするため、第一・第二引数にFalseを指定します。
その左上角セルのアドレスを引数にしてASC関数としますので、Ss値は「条件付き書式の適用先範囲の左上角セルの、全角にした値」を示す式が代入されることになります。

85行目「Sv = "Asc(""" & Me.TextBox1.Value & """)"」では、Sv側を作成しています。検索語として入力した値(Me.TextBox1.Value)を文字列として扱うために「”(ダブルクォーテーション」を二重にして検索語の前後に付けています。それをASC関数の引数にします。

「全角半角区別」にチェックが付いている場合は、87~93行目を実行します。
87行目「Ss = Rsearch(1).Address(False, False)」は、84行目に対してASC関数部分を外しただけです。
一方Sv値の方は、検索語が数値(半角)であるかでSv値を求める式を切り替えています。

寄り道
実は当初、Sv値を92行目「Sv = """" & Me.TextBox1.Value & """"」の式のみで試行していました。色々な検索語を使って検索結果が正しいかをチェックしてみると、図6-10のような結果でした。1箇所だけ正しく検索されない条件があるのです。
種類全角半角
数字半角×
数字全角
アルファベット半角小文字
アルファベット半角大文字
アルファベット全角小文字
アルファベット全角大文字
カタカナ半角
カタカナ全角
図6-10

④は「大文字小文字は区別せず」+「完全一致」+「半角と全角を区別」する条件で、図6-8の(C)である「等号」を使って条件付き書式の数式を組み立てている場面です。
例えば、A1セルを左上セルとする適用先に、検索語「100(半角)」で検索実行した時には、セル側が「数値の100」であっても検索はされません。
システムにより作成される条件付き書式の数式は「=A1="100"」で、一見良さそうに見えるのですが、数値と文字列の比較により結果はFalseとなります。
ちなみに「半角全角を区別しない」の条件③にすると、両辺にASC関数で処理がなされ「=ASC(A1)=ASC("100")」となり、文字列としての「"100"」がヒットすることになります。また、数字には無関係の「大文字小文字を区別」にレ点を入れる(⑧)と、処理する関数が変わり「=EXACT(A1,"100")」となるため、こちらもヒットします。

検索語が数値(半角)の場合だけ、数式として「=A1="100"」→「=A1=100」となる方法を考えた結果が、89行目「If Val(Me.TextBox1.Value) = Me.TextBox1.Value Then」で分岐させる方法です。Val関数ならば半角数字は数値に変換し、全角数字はゼロに変換します。
この手法を使うと、図6-10の表は「全て〇」となります。

89行目「If Val(Me.TextBox1.Value) = Me.TextBox1.Value Then」で、検索語が「半角の数字」である場合には90行目「Sv = Me.TextBox1.Value」で、「"(ダブルクォーテーション)」で両端を囲まない数値をSv値に代入します。条件付き書式の数式で言うと例えば「=A1=100」のような形になります。
一方、半角の数字以外の場合は92行目「Sv = """" & Me.TextBox1.Value & """"」で、検索語の両端に「"(ダブルクォーテーション)」を付けます。先程の数式で表すと、全角の「100」の場合だと「=A1="100"」のような形にします。

6-2-3-5.完全一致・大文字小文字ON-OFFに対する関数等への割り当て
97~106行目は、「大文字と小文字を区別」「完全一致」の検索条件のON-OFF組合せで、使う関数等を切り替え、数式を変数Fomulaに代入していきます。図6-11に使う関数等を整理し直します。
大文字小文字(CKB1)
完全一致(CKB2)
図6-7のNo.①②③④⑤⑥⑦⑧
(G)SEARCH(Sv,Ss)
(C)SS = Sv (等号)
(D)FIND(Sv,Ss)
(B)EXACT(Ss,Sv)
図6-11

図6-11の表の順序で、使う関数等に83~95行目で作成したSs値・Sv値をセットし、変数Fomulaに代入していきます。
CKB1とCKB2の両方がFalse(レ点がついて無い)の時には、99行目「Formula = "=Search(" & Sv & "," & Ss & ")"」を実行し、Search関数を使って「大文字小文字を区別せず」「部分一致」の検索を行います。このSearch関数の特性については、図6-8で確認願います。
CKB1がFalseで、CKB2がTrueの時には、101行目「Formula = "=" & Ss & "=" & Sv & ""」を実行します。等号を使いますので、「大文字小文字を区別せず」「完全一致」の検索をします。

CKB1がTrueで、CKB2がFalseの時には、103行目「Formula = "=Find(" & Sv & "," & Ss & ")"」を実行し、Find関数で「大文字小文字を区別」しながら「部分一致」となります。
CKB1とCKB2の両方がTrueの時には、105行目「Formula = "=Exact(" & Ss & "," & Sv & ")"」を実行し、Exact関数を使うことで「大文字小文字を区別」しながら「完全一致」の検索を行います。

6-2-3-6.条件付き書式の作成
108行目「With Rsearch.FormatConditions.Add(Type:=xlExpression, Formula1:=Formula)」では、作成した数式(=変数Fomula)を使って条件付き書式を作成します。適用する範囲は「Rsearch」のセル範囲です。

109行目「.SetFirstPriority」では、作った条件付き書式の優先度を1番にしています。
通常、手動で条件付き書式を作成する場合でも、この「SetFirstPriority」メソッドが自動的に働くために「新しく作った条件付き書式は、それまでに作った条件付き書式よりも優先度が高く(図6-12)」なります。これと同じことを109行目では実施しています。
条件付き書式の管理
図6-12

次に110行目「.Interior.Color = RGB(255, 0, 0)」で、条件付き書式の数式にヒットした時の「書式」を設定しています。好き嫌いがあると思いますが、今回は「背景色を赤色」に設定しました。

111行目「.StopIfTrue = True」では、「条件を満たす場合は停止」を有効にしています。このStopIfTrueプロパティをTrueに設定すると、図6-12のように「条件を満たす場合は停止」にレ点を付ける事になり、レ点が付いたルールが適用されたセルに対しては、それよりも優先度の低いルールは適用されなくなります。
なお、手動で条件付き書式を作成した場合は、自動的にStopIfTrue = False(レ点が付かない)となります。
もしレ点を付けない(False)と、図6-12のように優先度の低いルールの中に「ヒットしたら赤文字」になる条件付き書式が存在した時は、「赤文字+背景色も赤色」となり、文字が見えなくなってしまいます。そのため今回は、StopIfTrueプロパティをTrueにしています。

最後に113行目「IsMyFormula = True」で、条件付き書式存在フラグをONにしています。このフラグは、作られた条件付き書式を削除するか否かのフラグとなります。

6-2-4.条件付き書式の削除

今回システムで作成した条件付き書式を削除するのが図6-13です。
  1. '========== ⇩(9) 条件付き書式の削除 ============
  2. Private Sub FormulaDel()
  3.  If IsMyFormula = True Then
  4.   Rsearch.FormatConditions.Item(1).Delete
  5.   IsMyFormula = False
  6.  End If
  7. End Sub
図6-13

条件付き書式が作られる時には、図6-6の113行目でフラグ変数IsMyFormulaをTrueにしています(作られていない時の変数IsMyFormulaの既定値はFalse)。ですので118行目「If IsMyFormula = True Then」で、フラグ変数IsMyFormulaの値がTrueになっている時だけ、119行目で条件付き書式を削除します。

削除する条件付き書式のセル範囲は Rsearch ですが、この範囲内に「今回システム以外で作られた条件付き書式(手動で作ったものを含む)」が存在する可能性があります。そのため「Rsearch.FormatConditions.Delete」として全ての条件付き書式を削除してしまうと、既存の条件付き書式も一緒に削除されてしまいます。(既存の適用先セル範囲が広い場合は、Rsearchエリアの部分のみ適用先セル範囲が削られることになります。)

システムで作成した条件付き書式は、図6-6の109行目で「.SetFirstPriority」メソッドにより「優先度1番」にしていますので、119行目のように「Rsearch.FormatConditions.Item(1).Delete」とし、「1番目の条件付き書式のみを削除」します。
また120行目「IsMyFormula = False」では、「条件付き書式存在フラグ」である変数IsMyFormulaにFalseを設定し、フラグを降ろします。

図5-2の21行目から呼び出される「ユーザーがシートを移動した時に呼び出されるプロシージャ」が、図6-14です。
  1. '========== ⇩(10) ユーザー指定の検索範囲削除 ============
  2. Public Sub FormulaDelall()
  3.  Set Ruser = Nothing
  4.  Me.Label1.Caption = "ここをクリック"
  5.  Call FormulaDel
  6. End Sub
図6-14

ユーザーがシート移動した時には、「ユーザーが指定した検索範囲を削除」させています。ですので126行目「Set Ruser = Nothing」でユーザーの検索指定範囲「Ruser」を解除します。そしてフォームの検索範囲枠の表示文字列を127行目「Me.Label1.Caption = "ここをクリック"」で、起動直後と同じにしています。

また、ユーザー指定の検索範囲が無くなったのですから、作成した条件付き書式が存在しているのであれば消去する必要があります。129行目「Call FormulaDel」では図6-13を呼出し、条件付き書式を削除しています。

6-2-5.その他のボタン等の操作

フォーム上の「色付け取消」ボタンをクリックした時に呼び出されるのが図6-15です。
色付けを取り消すために、134行目「Call FormulaDel」で図6-13を呼出し、条件付き書式を削除しています。
  1. '========== ⇩(11) 色付け取消ボタン ============
  2. Private Sub CommandButton2_Click()
  3.  Call FormulaDel
  4. End Sub
図6-15

なお今回システムには盛り込みませんでしたが、「検索した結果を残したい」と言う場合には、それ用のボタンを準備し「IsMyFormula = False」を実行すれば「システムが作った条件付き書式が残る」ことになります。但し、この操作を実行後、更に検索を行おうとすると「背景色が赤色のルール」が複数存在してしまうので、どれが残したかった結果で、どれが更に検索した結果なのかが分からなくなりますので、使うときには注意が必要です。また複数回「検索結果を残す」処理をしても同様の問題が発生します。

フォーム上の「終了」ボタンをクリックした時に呼び出されるのが図6-16です。
140行目「Unload Me」でシステムを終了しています。フォームを閉じる時には、図6-17のQueryCloseイベントを通過しますので、そちらで「システムが作成した条件付き書式の削除」を行っています。
  1. '========== ⇩(12) 終了ボタン ============
  2. Private Sub CommandButton3_Click()
  3.  Unload Me
  4. End Sub
図6-16

フォーム右上×印をクリックしてフォームを閉じようとした時に呼び出されるのが図6-17です。また「終了」ボタンによりフォームを閉じる時も、このQueryCloseイベントを通過します。
  1. '========== ⇩(13) フォーム右上×印で終了 ============
  2. Private Sub UserForm_QueryClose(Cancel As Integer, CloseMode As Integer)
  3.  Call FormulaDel
  4.  Set AnotherEvent.mySheet = Nothing
  5. End Sub
図6-17

145行目「Call FormulaDel」で図6-13を呼出し、条件付き書式を削除します。

146行目「Set AnotherEvent.mySheet = Nothing」では、図4-2の9行目でExcel.Applicationの代入をした「AnotherEvent.mySheet」の解除を行っています。この解除を行わないと、システム終了後も(Excelを終了するまで)ずっと図5-2の「ブック全体へのSheetActivateイベント」が発生してしまいます。イベントが発生してもフォーム上のプロシージャの呼出しをしているだけなので実害は有りませんが、無駄な動きであるためストップさせています。

このプロシージャ内では「Cancel = True」をしていないため、プロシージャ終了と共にそのままシステムが終了します。

7.アドインとしてExcelにマクロを登録

このマクロ付ファイル(サンプルファイル)をExcelのアドインに登録することで、今回システム「シート内検索結果を色付け表示」を利用することが出来ます。アドイン方法については「年賀状リスト等の宛名検索と追記 アドイン登録」を参照下さい。
また、リボン上のボタンには、図4-2の「SheetSearch」プロシージャをマクロ登録して下さい。

8.最後に

今回の文字列検索のために使った「SEARCH関数」は、図6-8でも分かるように「ワイルドカード( *、?、~ )」が使えます。つまり「大文字小文字区別」と「完全一致」の検索条件にレ点が付いていなければ、ワイルドカードを使って検索可能です。
しかし他の3種の関数等ではワイルドカードが使えないため、「高度な検索方法」を御存知の方が今回システムを使用すると、検索条件のどこにレ点を入れるかで「ワイルドカードが使えたり使えなかったり」してしまい、返って使い難くなってしまうかもしれません。

とは言え、検索条件を網羅した形のシステムにするために集めた関数等が「大文字小文字の区別有無」「完全一致・部分一致」にうまく分かれてくれてホッとしました。



シート内検索結果を色付け表示(it-067.xlsm)
セキュリティ向上を目的として「インターネット経由でダウンロードしたOfficeファイル(Excel等)のマクロは、既定でブロック」されるようにOfficeアプリケーションの既定動作が変更になりました。(2022年4月より切替開始)
解除の方法については「ダウンロードファイルのブロック解除方法」を参照下さい。