2021/10/17

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

• １．背景
• ２．システム概要
• ３．プログラムの流れ
• ４．標準モジュール（Module1）
• ５．クラスモジュール（Class1）
• ６．ユーザーフォーム（UserForm1）
• 　６－１．レイアウト
• 　６－２．フォームモジュール
• 　　６－２－１．起動時初期設定
• 　　６－２－２．検索対象範囲の設定
• 　　６－２－３．検索の実行
• 　　　６－２－３－１．検索範囲のチェック
• 　　　６－２－３－２．既存の条件付き書式の削除
• 　　　６－２－３－３．条件付き書式に使う関数等の選定
• 　　　　よりみち（FIND関数の半角全角区別）
• 　　　６－２－３－４．全角半角区別ON-OFFに対する処理
• 　　　　よりみち（数値の検索）
• 　　　６－２－３－５．完全一致・大文字小文字ON-OFFに対する関数等への割り当て
• 　　　６－２－３－６．条件付き書式の作成
• 　　６－２－４．条件付き書式の削除
• 　　６－２－５．その他のボタン等の操作
• ７．アドインとしてExcelにマクロを登録
• ８．最後に
• サンプルファイル

１．背景

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

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

２．システム概要

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

図２－１

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

図２－２

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

図２－３

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

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

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

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

図２－４

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

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

３．プログラムの流れ

今回システムは、フォームの内容（検索語＋検索条件３種）を条件付き書式の数式として組み立て、検索範囲（ユーザー設定の範囲とUsedRangeとが重複する範囲）に対して設定することで、条件付き書式の数式がTrueになったセルを条件付き書式の書式（今回は背景色＝赤色）に変えています。

図３－１

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

４．標準モジュール（Module1）

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

まず標準モジュールの宣言部（図４－１）では、２行目「Private AnotherEvent As Class1」で、モジュールレベル変数として「クラスの変数宣言」をしています。

1. '==========　⇩(1)　クラスの変数宣言　============
2. Public AnotherEvent As Class1

図４－１

次に、Excelアドインのボタン等から呼び出される起動プロシージャが図４－２です。ボタンに登録するマクロとしては、この「SheetSearch」を指定して下さい。

5. '==========　⇩(2)　システム起動　============
6. Public Sub SheetSearch()
8. 　Set AnotherEvent = New Class1
9. 　Set AnotherEvent.mySheet = Excel.Application
11. 　UserForm1.Show vbModeless
13. End Sub

図４－２

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

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

５．クラスモジュール（Class1）

クラスモジュールの先頭部では、WithEventsを使いイベントに対応するためのオブジェクト変数の定義をしています（図５－１の１６行目）。

15. '==========　⇩(3)　オブジェクト変数の定義　============
16. Public WithEvents mySheet As Application

図５－１

１６行目で定義をしたオブジェクト変数「mySheet」は、図４－２の９行目でExcelアプリケーション全てが対象となります。そのExcelアプリケーションの（つまりは、自分も含めて全てのExcelの）SheetActivateイベントが発生した時に呼び出されるのが図５－２です。

18. '==========　⇩(4)　シート移動した時のイベント　============
19. Private Sub mySheet_SheetActivate(ByVal Sh As Object)
21. 　Call UserForm1.FormulaDelall
23. End Sub

図５－２

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

６．ユーザーフォーム（UserForm1）

６－１．レイアウト

検索フォームは、図６－１のようなレイアウトにしました。

図６－１

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

６－２．フォームモジュール

６－２－１．起動時初期設定

フォーム内で共通して使用する変数を図６－２のように宣言部で宣言しています。

25. '==========　⇩(5)　フォームレベルの共通変数の宣言　============
26. Dim Ruser As Range 　　　　　　　 'ユーザーが指定したセル範囲（Range userの略）
27. Dim Rsearch As Range 　　　　　　 '条件付き書式の適用範囲（Range searchの略）
28. Dim IsMyFormula As Boolean 　　　 '今回システムが作った条件付き書式が存在するか否かのフラグ

図６－２

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

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

フォーム起動時に最初に実行されるのが図６－３です。

30. '==========　⇩(6)　フォーム初期設定　============
31. Private Sub UserForm_Initialize()
32. 　Me.Caption = "シート内検索結果を色付け表示"
33. 　Me.Label1.BorderStyle = fmBorderStyleSingle
34. 　Me.Label1.Caption = "ここをクリック"
35. End Sub

図６－３

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

６－２－２．検索対象範囲の設定

フォーム上のLabel1の部分をクリックした時に呼び出されるのが図６－４です。

37. '==========　⇩(7)　検索範囲指定　============
38. Private Sub Label1_Click()
39. 　On Error Resume Next
40. 　　Set Ruser = Application.InputBox("検索対象範囲を選択して下さい", Type:=8)
42. 　　If Err.Number = 0 Then
43. 　　　Call FormulaDel
44. 　　　Label1.Caption = Ruser.Address
45. 　　End If
47. 　On Error GoTo 0
48. End Sub

図６－４

４０行目「Set Ruser = Application.InputBox("検索対象範囲を選択して下さい", Type:=8)」では、InputBoxメソッドを表示し、ユーザーに検索範囲を指定してもらいます。InputBoxメソッドの第一引数はInputBoxに表示される文字列ですが、第二引数（名前付き引数「Type:=」で指定した引数）は、図６－５から指定します。

InputBoxメソッドの引数Typeの値

値	説明
0	数式
1	数値
2	文字列（テキスト）既定値
4	理論値(True 又は False)
8	セル参照（Rangeオブジェクト）
16	#N/Aなどのエラー値
64	値の配列

図６－５

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

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

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

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

６－２－３．検索の実行

「検索実行」ボタンをクリックすると、図６－６が呼び出されます。

50. '==========　⇩(8)　検索実行　============
51. Private Sub CommandButton1_Click()
52. 　Dim CKB1 As Boolean 　　 'CheckBox1(大文字小文字を区別)の値
53. 　Dim CKB2 As Boolean 　　 'CheckBox2(完全一致)の値
54. 　Dim CKB3 As Boolean 　　 'CheckBox3(全角半角を区別)の値
55. 　Dim Formula As String 　 '条件付き書式の数式
56. 　Dim Ss As String 　　　 '検索元(search Sourceの略)
57. 　Dim Sv As String 　　　 '検索語(search Valueの略)
59. 　On Error Resume Next
60. 　　With ActiveSheet
61. 　　　Set Rsearch = Intersect(Ruser, .UsedRange)
63. 　　　If Not Err.Number = 0 Then
64. 　　　　MsgBox "検索範囲を設定後、検索開始して下さい。"
65. 　　　　Exit Sub
66. 　　　ElseIf WorksheetFunction.CountBlank(.UsedRange) = .UsedRange.Count Then
67. 　　　　MsgBox "このシートにはデータが存在しません。"
68. 　　　　Exit Sub
69. 　　　ElseIf Rsearch Is Nothing Then
70. 　　　　MsgBox "指定した範囲にはデータが存在しません。再設定して下さい。"
71. 　　　　Exit Sub
72. 　　　End If
74. 　　End With
75. 　On Error GoTo 0
77. 　Call FormulaDel
79. 　CKB1 = Me.CheckBox1.Value
80. 　CKB2 = Me.CheckBox2.Value
81. 　CKB3 = Me.CheckBox3.Value
83. 　If CKB3 = False Then
84. 　　Ss = "Asc(" & Rsearch(1).Address(False, False) & ")"
85. 　　Sv = "Asc(""" & Me.TextBox1.Value & """)"
86. 　Else
87. 　　Ss = Rsearch(1).Address(False, False)
89. 　　If Val(Me.TextBox1.Value) = Me.TextBox1.Value Then
90. 　　　Sv = Me.TextBox1.Value
91. 　　Else
92. 　　　Sv = """" & Me.TextBox1.Value & """"
93. 　　End If
95. 　End If
97. 　Select Case True
98. 　　Case CKB1 = False And CKB2 = False
99. 　　　Formula = "=Search(" & Sv & "," & Ss & ")"
100. 　　Case CKB1 = False And CKB2 = True
101. 　　　Formula = "=" & Ss & "=" & Sv & ""
102. 　　Case CKB1 = True And CKB2 = False
103. 　　　Formula = "=Find(" & Sv & "," & Ss & ")"
104. 　　Case CKB1 = True And CKB2 = True
105. 　　　Formula = "=Exact(" & Ss & "," & Sv & ")"
106. 　End Select
108. 　With Rsearch.FormatConditions.Add(Type:=xlExpression, Formula1:=Formula)
109. 　　.SetFirstPriority
110. 　　.Interior.Color = RGB(255, 0, 0)
111. 　　.StopIfTrue = True
112. 　End With
113. 　IsMyFormula = True
115. End Sub

図６－６

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

６－２－３－１．検索範囲のチェック

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

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

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

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

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

６－２－３－２．既存の条件付き書式の削除

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

６－２－３－３．条件付き書式に使う関数等の選定

８３行目以降で条件付き書式の数式を作成するのですが、まず「検索条件」は今回３種類準備しましたので、そのON-OFFの組み合わせは図６－７のように８種類（①～⑧）となります。（レ点有りがON状態）

変数	検索条件	①	②	③	④	⑤	⑥	⑦	⑧
CKB1	大文字小文字を区別					レ
CKB2	完全一致			レ				レ
CKB3	半角全角を区別		レ		レ		レ		レ

図６－７

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

検索される側＝Ss、検索語＝Sv　とする

No.	関数など	Ssの型	大文字小文字	完全一致?	全角半角	ワイルドカード	成立時	非成立時
(A)	COUNTIF(Ss,Sv)	Range型	区別せず	完全	区別する	可	1	0
(B)	EXACT(Ss,Sv)	String型	区別する	完全	区別する	不可	TRUE	FALSE
(C)	SS = Sv　(等号で結ぶ)	何でも	区別せず	完全	区別する	不可	TRUE	FALSE
(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!

図６－８

一部のサイトや書籍に「FIND関数は、半角文字と全角文字は区別されません。例えば、半角「A」と全角「Ａ」は同じ文字とみなされます。」との表記があります。 あれ？と思って色々試してみたのですが、ちゃんと区別しているようですし、Microsoftサポートのページにもそのような記述は見つかりませんでした。他項目の説明文のコピペ間違いか、または「半角も全角も区別せず、１文字を１として処理する」との意味で書いているのかもしれません。

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

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

検索条件と関数等を整理したのが図６－９です。
「ASC関数」を使うことを前提に考えれば、図６－９の赤丸を使う事で「３種の検索条件が網羅」出来ることになります。

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)	〇	〇

図６－９

６－２－３－４．全角半角区別ON-OFFに対する処理

まず８３～９５行目では「全角半角区別」のチェック有無により、セル範囲側・検索語側へのASC関数の加工有無を分岐します。
８３行目「If CKB3 = False Then」では「全角半角区別にチェック無し」の場合に８４～８５行目を実行し、「セル範囲側・検索語側の双方にASC関数を付ける」ことで「全角半角区別しない状態」にしています。
数式の形としては「=A1="ABC"」のようにしますので、「A1」側を変数Ssとし「"ABC"」側を変数Svとします。

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

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

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

実は当初、Sv値を９２行目「Sv = """" & Me.TextBox1.Value & """"」の式のみで試行していました。色々な検索語を使って検索結果が正しいかをチェックしてみると、図６－１０のような結果でした。１箇所だけ正しく検索されない条件があるのです。 種類 全角半角 ① ② ③ ④ ⑤ ⑥ ⑦ ⑧ 数字 半角 〇 〇 〇 × 〇 〇 〇 〇 数字 全角 〇 〇 〇 〇 〇 〇 〇 〇 ｱﾙﾌｧﾍﾞｯﾄ 半角小文字 〇 〇 〇 〇 〇 〇 〇 〇 ｱﾙﾌｧﾍﾞｯﾄ 半角大文字 〇 〇 〇 〇 〇 〇 〇 〇 ｱﾙﾌｧﾍﾞｯﾄ 全角小文字 〇 〇 〇 〇 〇 〇 〇 〇 ｱﾙﾌｧﾍﾞｯﾄ 全角大文字 〇 〇 〇 〇 〇 〇 〇 〇 ｶﾀｶﾅ 半角 〇 〇 〇 〇 〇 〇 〇 〇 ｶﾀｶﾅ 全角 〇 〇 〇 〇 〇 〇 〇 〇 図６－１０ ④は「大文字小文字は区別せず」＋「完全一致」＋「半角と全角を区別」する条件で、図６－８の(C)である「等号」を使って条件付き書式の数式を組み立てている場面です。 例えば、A1セルを左上セルとする適用先に、検索語「100（半角）」で検索実行した時には、セル側が「数値の100」であっても検索はされません。 システムにより作成される条件付き書式の数式は「=A1="100"」で、一見良さそうに見えるのですが、数値と文字列の比較により結果はFalseとなります。 ちなみに「半角全角を区別しない」の条件③にすると、両辺にASC関数で処理がなされ「=ASC(A1)=ASC("100")」となり、文字列としての「"100"」がヒットすることになります。また、数字には無関係の「大文字小文字を区別」にレ点を入れる（⑧）と、処理する関数が変わり「=EXACT(A1,"100")」となるため、こちらもヒットします。 検索語が数値（半角）の場合だけ、数式として「=A1="100"」→「=A1=100」となる方法を考えた結果が、８９行目「If Val(Me.TextBox1.Value) = Me.TextBox1.Value Then」で分岐させる方法です。Val関数ならば半角数字は数値に変換し、全角数字はゼロに変換します。 この手法を使うと、図６－１０の表は「全て〇」となります。

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

６－２－３－５．完全一致・大文字小文字ON-OFFに対する関数等への割り当て

９７～１０６行目は、「大文字と小文字を区別」「完全一致」の検索条件のON-OFF組合せで、使う関数等を切り替え、数式を変数Fomulaに代入していきます。図６－１１に使う関数等を整理し直します。

大文字小文字（CKB1）				レ
完全一致（CKB2）			レ		レ
図６－７のNo.		①②	③④	⑤⑥	⑦⑧
(G)	SEARCH(Sv,Ss)	〇
(C)	SS = Sv　(等号)		〇
(D)	FIND(Sv,Ss)			〇
(B)	EXACT(Ss,Sv)				〇

図６－１１

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

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

６－２－３－６．条件付き書式の作成

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

１０９行目「.SetFirstPriority」では、作った条件付き書式の優先度を１番にしています。
通常、手動で条件付き書式を作成する場合でも、この「SetFirstPriority」メソッドが自動的に働くために「新しく作った条件付き書式は、それまでに作った条件付き書式よりも優先度が高く（図６－１２）」なります。これと同じことを１０９行目では実施しています。

図６－１２

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

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

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

６－２－４．条件付き書式の削除

今回システムで作成した条件付き書式を削除するのが図６－１３です。

116. '==========　⇩(9)　条件付き書式の削除　============
117. Private Sub FormulaDel()
118. 　If IsMyFormula = True Then
119. 　　Rsearch.FormatConditions.Item(1).Delete
120. 　　IsMyFormula = False
121. 　End If
122. End Sub

図６－１３

条件付き書式が作られる時には、図６－６の１１３行目でフラグ変数IsMyFormulaをTrueにしています（作られていない時の変数IsMyFormulaの既定値はFalse）。ですので１１８行目「If IsMyFormula = True Then」で、フラグ変数IsMyFormulaの値がTrueになっている時だけ、１１９行目で条件付き書式を削除します。

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

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

図５－２の２１行目から呼び出される「ユーザーがシートを移動した時に呼び出されるプロシージャ」が、図６－１４です。

124. '==========　⇩(10)　ユーザー指定の検索範囲削除　============
125. Public Sub FormulaDelall()
126. 　Set Ruser = Nothing
127. 　Me.Label1.Caption = "ここをクリック"
129. 　Call FormulaDel
130. End Sub

図６－１４

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

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

６－２－５．その他のボタン等の操作

フォーム上の「色付け取消」ボタンをクリックした時に呼び出されるのが図６－１５です。
色付けを取り消すために、１３４行目「Call FormulaDel」で図６－１３を呼出し、条件付き書式を削除しています。

132. '==========　⇩(11)　色付け取消ボタン　============
133. Private Sub CommandButton2_Click()
134. 　Call FormulaDel
135. End Sub

図６－１５

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

フォーム上の「終了」ボタンをクリックした時に呼び出されるのが図６－１６です。
１４０行目「Unload Me」でシステムを終了しています。フォームを閉じる時には、図６－１７のQueryCloseイベントを通過しますので、そちらで「システムが作成した条件付き書式の削除」を行っています。

137. '==========　⇩(12)　終了ボタン　============
138. Private Sub CommandButton3_Click()
140. 　Unload Me
141. End Sub

図６－１６

フォーム右上×印をクリックしてフォームを閉じようとした時に呼び出されるのが図６－１７です。また「終了」ボタンによりフォームを閉じる時も、このQueryCloseイベントを通過します。

143. '==========　⇩(13)　フォーム右上×印で終了　============
144. Private Sub UserForm_QueryClose(Cancel As Integer, CloseMode As Integer)
145. 　Call FormulaDel
146. 　Set AnotherEvent.mySheet = Nothing
147. End Sub

図６－１７

１４５行目「Call FormulaDel」で図６－１３を呼出し、条件付き書式を削除します。

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

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

７．アドインとしてExcelにマクロを登録

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

８．最後に

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

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



シート内検索結果を色付け表示（it-067.xlsm）

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