セルの強調表示ルール

一覧表を色付けしたりして見易くする方法として、条件付き書式と言うのがあります

これって、各シートのセルや範囲に対して複数の設定ができるのですが、どのように管理されているのだろうか・・

■種類

まずはExcelのスタイルにある「条件付き書式」を開いてみます

条件付き書式
セルの強調表示ルール
条件付き書式
広告

簡単なものであれば、デフォルトで用意されている書式ルールを利用しても充分です

今回は「セルの強調表示ルール」について順に書式ルール毎に見ていきましょう

■セルの強調表示ルール

これはセルの値に応じて、セルを強調表示させるためのルールです

条件付き書式-セルの強調表示ルール
セルの強調表示ルール

一番下にある「その他のルール」を選択した場合は、以下のような新しい書式ルールの画面が表示されます

セルの強調表示ルール 1
その他のルール

よくある試験の点数表のようなデータをサンプルにして、条件付き書式を施してみます

条件付き書式-サンプルデータ
サンプルデータ

基本的な条件付き書式の対象範囲は、上図のサンプルデータの背景色が白い点数部分を対象として行います

範囲 [Rangeオブジェクト] で言うところの「C4:K8」ですね

ま、実施日と言う日付行を作っているのは、察してくださいｗ

・指定の値より大きい

その名の通り、指定した値より大きいセルに対して、指定した書式を施します

条件付き書式-指定の値より大きい

点数の白背景部分を全部選択して、「指定の値より大きい」を選択すると、何故だか「84.5」と言う値が自動で入りました

この値は謎ですが、そのまま採用した結果が以下になります

条件付き書式-指定の値より大きいサンプル
指定の値より大きいサンプル

– 書式ルールを確認

これがどの様な書式ルールになっているのかを確認するため、「ルールの管理」から「ルールの編集」を開きます

条件付き書式-指定の値より大きいの編集
指定の値より大きいの編集

– マクロで記録してみる

セルの選択から「指定の値より大きい」を選択して、設定するところまでを「マクロの記録」で記録してみました

すると、こんな感じにVBAソースが出力されました

Sub Macro1()
'
' Macro1 指定の値より大きい
'

'
    Range("C4:K8").Select
    Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlGreater, _
        formula1:="=84.5"
    Selection.FormatConditions(Selection.FormatConditions.count).SetFirstPriority
    With Selection.FormatConditions(1).Font
        .Color = -16383844
        .TintAndShade = 0
    End With
    With Selection.FormatConditions(1).Interior
        .PatternColorIndex = xlAutomatic
        .Color = 13551615
        .TintAndShade = 0
    End With
    Selection.FormatConditions(1).StopIfTrue = False
End Sub

– マクロの説明

・簡単に説明！

7行目：セルの選択

8-9行目：条件付き書式の追加

10行目：優先順位の設定

11-14行目：フォントの設定

15-19行目：背景の設定

20行目：条件を満たす場合は停止するかどうか

・もう少し詳しく！

①ここで重要なのは、8-9行目の条件付き書式の追加で使用される

[FormatConditionsコレクション] の [Addメソッド] です

セルの強調表示ルール 2

「書式ルールを確認」と見比べてください！

セルの強調表示ルール 3

ルールの種類は「指定の値を含むセルだけを書式設定」が選択されていました

[Addメソッド] がこの「指定の値を含むセルだけを書式設定」を意味しています

※つまり、他のルールの種類の場合は、 [Addメソッド] とは異なります！

②次に、その[Addメソッド]のパラメータを見ていきます

セルの強調表示ルール 2

これが「書式ルールを確認」の「次のセルのみを書式設定」の部分と一致します

セルの強調表示ルール 5
  • Type:=xlCellValue は「セルの値」
  • Operator:=xlGreater は「次の値より大きい」
  • formula1:=”=84.5″ は「値」が等しい(=)

『「セルの値」が「84.5と等しい値」「より大きい」』という意味になりますが、「等しい＋より大きい」は「以上(>=)」ではないので注意してください

あくまでも「等しい値＋より大きい(>)」です！

③残りの書式ルールはフォントと背景なのですが・・その前に１行あります

10行目の優先順位の設定です

セルの強調表示ルール 6

解り難い一行かも知れませんが、この一行の肝は、 [SetFirstPriorityメソッド]ではなく、実は [FormatConditions.countプロパティ] です！

Addした書式ルールは、 [FormatConditionsコレクション] の一番最後（インデックス番号が 「FormatConditions.count」 と一致するアイテム）に追加されます

その書式ルールを[SetFirstPriorityメソッド]で先頭（最優先＝１）にしています

そのため、この後に出てくるフォントと背景の設定は、 [FormatConditionsコレクション] の１番目（「FormatConditions(1)」 ）のアイテムに対して実施されます

④では、フォントの設定です（「FormatConditions(1)」 の意味は、上記で理解頂いたと思いますので、もう触れませんｗ）

セルの強調表示ルール 7

フォント名やサイズは変えず、色 [Colorプロパティ] だけ変えていますが、 [TintAndShadeプロパティ] が設定されています

これは、明度（-1：暗い～1：明るい、0は標準）で、Single型の単精度浮動小数点の値が設定可能です

肝心の文字色を表す [Colorプロパティ] ですが、「-16383844」とは何でしょうか？

これはフォント色のカラーコードを10進数で表現した値になります

※詳細は説明は、このページの最後に書いています

⑤残りの書式は、背景の設定です

セルの強調表示ルール 8

「.PatternColorIndex = xlAutomatic」 は塗り潰しパターン色を自動に設定するの意味です

セルの書式設定の画面で言う右上の「パターンの色」が「自動」となっているこの部分です

セルの強調表示ルール 9

⑥最後の１行、条件を満たす場合は停止するかどうかについては、書式ルールの画面ではなく、ルールの管理画面にある「条件を満たす場合は停止」の部分の設定になります

セルの強調表示ルール 10

デフォルトでは「False」（つまり、チェックしない）です

セルの強調表示ルール 11

かなり詳しく説明してしまいました・・ｗ

他の書式ルールも同じ粒度で書いてたら、恐ろしいことになるので、以下では差異のある変更点等にのみ着目して説明していきます

・指定の値より小さい

その名の通り、指定した値より小さいセルに対して、指定した書式を施します

条件付き書式-指定の値より小さい

こちらも「指定の値より小さい」を選択して、値を「84.5」とした結果が以下になります

条件付き書式-指定の値より小さいサンプル
指定の値より小さいサンプル

– 書式ルールを確認

条件付き書式-指定の値より小さいの編集
指定の値より小さいの編集

– マクロで記録してみる

さて、「指定の値より大きい」と「指定の値より小さい」とのマクロの違いは何でしょうか？

当然！「大きい」と「小さい」の差だけですねー

Sub Macro2()
'
' Macro2 指定の値より小さい
'
'
    Range("C4:K8").Select
    Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlLess, _
        formula1:="=84.5"
    Selection.FormatConditions(Selection.FormatConditions.count).SetFirstPriority
    With Selection.FormatConditions(1).Font
        .Color = -16383844
        .TintAndShade = 0
    End With
    With Selection.FormatConditions(1).Interior
        .PatternColorIndex = xlAutomatic
        .Color = 13551615
        .TintAndShade = 0
    End With
    Selection.FormatConditions(1).StopIfTrue = False
End Sub

– マクロの説明

・異なるところだけを説明

※VBAソースの詳細な説明は、「指定の値より大きい」の説明を見てくださいｗ

8-9行目：条件付き書式の追加

  • Type:=xlCellValue は「セルの値」
  • Operator:=xlLess は「次の値より小さい」
  • formula1:=”=84.5″　は「値」が等しい(=)

・指定の範囲内

これは、2つの値の範囲に含まれる場合に、指定した書式を施します

条件付き書式-指定の範囲内

こちらも「指定の値より小さい」を選択して、値を「84.5」とした結果が以下になります

条件付き書式-指定の範囲内サンプル
指定の範囲内サンプル

– 書式ルールを確認

条件付き書式-指定の範囲内の編集
指定の範囲内の編集

– マクロで記録してみる

選択範囲と書式は変えていませんので、「指定の範囲内」では、以下が異なります

Sub Macro3()
'
' Macro3 指定の範囲内
'

'
    Range("C4:K8").Select
    Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlBetween, _
        formula1:="=79", formula2:="=90"
    Selection.FormatConditions(Selection.FormatConditions.count).SetFirstPriority
    With Selection.FormatConditions(1).Font
        .Color = -16383844
        .TintAndShade = 0
    End With
    With Selection.FormatConditions(1).Interior
        .PatternColorIndex = xlAutomatic
        .Color = 13551615
        .TintAndShade = 0
    End With
    Selection.FormatConditions(1).StopIfTrue = False
End Sub

– マクロの説明

・異なるところだけを説明

※VBAソースの詳細な説明は、「指定の値より大きい」の説明を見てくださいｗ

8-9行目にあたる部分です

  • Type:=xlCellValue は「セルの値」
  • Operator:=xlBetween は「次の値の間」
  • formula1:=”=79″ は最小「値」が等しい(=)
  • formula2:=”=90″ は最大「値」が等しい(=)

・指定の値に等しい

その名の通り、指定した値と等しいセルに対して、指定した書式を施します

条件付き書式-指定の値に等しい

こちらも「指定の値に等しい」を選択した場合に、値が「84.5」と自動入力されましたが、等しいのがなかったので、「82」に変更した結果が以下になります

条件付き書式-指定の値に等しいサンプル
指定の値に等しいサンプル

– 書式ルールを確認

条件付き書式-指定の値に等しいの編集
指定の値に等しいの編集
広告

– マクロで記録してみる

「指定の値に等しい」は「大きい」や「小さい」と同じように値は１つですね

Sub Macro4()
'
' Macro4 指定の値に等しい
'

'
    Range("C4:K8").Select
    Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlEqual, _
        formula1:="=82"
    Selection.FormatConditions(Selection.FormatConditions.count).SetFirstPriority
    With Selection.FormatConditions(1).Font
        .Color = -16383844
        .TintAndShade = 0
    End With
    With Selection.FormatConditions(1).Interior
        .PatternColorIndex = xlAutomatic
        .Color = 13551615
        .TintAndShade = 0
    End With
    Selection.FormatConditions(1).StopIfTrue = False
End Sub

– マクロの説明

・異なるところだけを説明

※VBAソースの詳細な説明は、「指定の値より大きい」の説明を見てくださいｗ

8-9行目にあたる部分です

  • Type:=xlCellValue は「セルの値」
  • Operator:=xlEqual は「次の値に等しい」
  • formula1:=”=82″ は「値」が等しい(=)

・文字列

これは、指定の文字列を含むセルに対して、指定した書式を施します

条件付き書式-文字列

テスト結果は数字だけでしたので、選択を範囲を広げて、文字列に「試験」を設定した結果が以下になります

条件付き書式-文字列サンプル
文字列サンプル

– 書式ルールを確認

条件付き書式-文字列の編集
文字列の編集

– マクロで記録してみる

選択範囲を変えてますけど、そこはそっとしておいてーｗ

Sub Macro5()
'
' Macro5 文字列
'

'
    Range("B2:M10").Select
    Selection.FormatConditions.Add Type:=xlTextString, String:="試験", _
        TextOperator:=xlContains
    Selection.FormatConditions(Selection.FormatConditions.count).SetFirstPriority
    With Selection.FormatConditions(1).Font
        .Color = -16383844
        .TintAndShade = 0
    End With
    With Selection.FormatConditions(1).Interior
        .PatternColorIndex = xlAutomatic
        .Color = 13551615
        .TintAndShade = 0
    End With
    Selection.FormatConditions(1).StopIfTrue = False
End Sub

– マクロの説明

・異なるところだけを説明

※VBAソースの詳細な説明は、「指定の値より大きい」の説明を見てくださいｗ

「文字列」は特殊なようで、[Addメソッド]のパラメータが文字列ように変更されています

  • Type:=xlTextString は「特定の文字列」
  • String:=”試験” は文字列の「値」
  • TextOperator:=xlContains は「次の値を含む」

・日付

その名の通りと言いたいところですが、実際には指定した日付の期間内のセルに対して、指定した書式を施します

条件付き書式-日付

「日付」を選択した場合、期間が選択できるようになり、この期間で「先月」を選択した結果が以下になります

条件付き書式-日付サンプル
日付サンプル

– 書式ルールを確認

条件付き書式-日付の編集
日付の編集

– マクロで記録してみる

予定通りの日付までを範囲に含めて条件付き書式を設定しています

Sub Macro6()
'
' Macro6 日付
'

'
    Range("B2:M10").Select
    Selection.FormatConditions.Add Type:=xlTimePeriod, DateOperator:=xlLastMonth
    Selection.FormatConditions(Selection.FormatConditions.count).SetFirstPriority
    With Selection.FormatConditions(1).Font
        .Color = -16383844
        .TintAndShade = 0
    End With
    With Selection.FormatConditions(1).Interior
        .PatternColorIndex = xlAutomatic
        .Color = 13551615
        .TintAndShade = 0
    End With
    Selection.FormatConditions(1).StopIfTrue = False
End Sub

– マクロの説明

・異なるところだけを説明

※VBAソースの詳細な説明は、「指定の値より大きい」の説明を見てくださいｗ

[Addメソッド]のパラメータが日付用に変わっています

  • Type:=xlTimePeriod は「日付」
  • DateOperator:=xlLastMonth は「先月」

・重複する値

その名の通り、指定した範囲で重複する値のセルに対して、指定した書式を施します

条件付き書式-重複する値

「重複する値」を選択した場合、次の値を選択できるようになり、ここで「重複」を選択した結果が以下になります

条件付き書式-重複する値サンプル
重複する値サンプル

– 書式ルールを確認

条件付き書式-重複する値の編集
重複する値の編集

– マクロで記録してみる

「重複する値」は値を入力ではなく、日付のように選択があるだけです

Sub Macro7()
'
' Macro7 重複する値
'

'
    Range("C4:K8").Select
    Selection.FormatConditions.AddUniqueValues
    Selection.FormatConditions(Selection.FormatConditions.count).SetFirstPriority
    Selection.FormatConditions(1).DupeUnique = xlDuplicate
    With Selection.FormatConditions(1).Font
        .Color = -16383844
        .TintAndShade = 0
    End With
    With Selection.FormatConditions(1).Interior
        .PatternColorIndex = xlAutomatic
        .Color = 13551615
        .TintAndShade = 0
    End With
    Selection.FormatConditions(1).StopIfTrue = False
End Sub

– マクロの説明

・異なるところだけを説明

※VBAソースの詳細な説明は、「指定の値より大きい」の説明を見てくださいｗ

かなり特殊なようです

[Addメソッド]ではなく、[AddUniqueValuesメソッド]が使用されています

つまりは、[AddUniqueValuesメソッド]が「一意の値または重複する値だけを書式設定」を意味しています

フォントや背景の設定の前に、[DupeUniqueプロパティ]の設定が追加されています

  • DupeUnique = xlDuplicate は「重複」

■最後に

一つずつ違いを見ていくと、かなり時間がかかってしまいました

今回は、「セルの強調表示ルール」のみで終わりにします

次回は、「上位/下位ルール」を調べます

・色の話

この話をココで詳しく話すよりは、どこかのサイトを見てもらった方がいいのですが・・ｗ

色と言うのは、16進数の6桁（つまり6桁*4bit=）24ビットカラー（フルカラー）で表現されるので、「0x000000 (0)」～「0xFFFFFF (‭16,777,215‬)」の合計：16,777,216 (「0x1000000」)色あるのです！

※16進数の「F」は、10進数では「15」、2進数では「1111」(4bit)

条件付き書式のフォント設定で出てきた「-16383844」(10進数)について説明します！

セルの強調表示ルール 12

これはフォントの色の設定を見て頂くと出てきますが、Hexに記載されている16進数の「#9C0006」をちょっといじって10進数で表した値ですｗ

セルの強調表示ルール 13

このHexの「#9C0006」はRGB(赤=156、緑=0、青=6)のカラーコード表現になるので、16進数では赤が「0x9C」、緑が「0x00」、青が「0x06」となります

これをつなげたのが、「9C0006」(16進数)です

では、フォント設定のVBAソースを以下のようにして、[Colorプロパティ]の値を出力してみます

    With Selection.FormatConditions(1).Font
        .Color = -16383844
        Debug.Print .Color        ' ①「 393372 」と出力
        Debug.Print Hex(.Color)   ' ②「6009C」と出力
        .TintAndShade = 0
    End With

[Colorプロパティ]に「-16383844」を設定しているのに、それを出力すると、「393372」と出力されました！

少し上で書いた「合計：16,777,216 (「0x1000000」)色」を思い出してください

セルの強調表示ルール 14

『(色の合計数) – [Colorプロパティ] = (①のDebug.Printの出力)』となります

そしてもう一つの②「6009C」と出力されたのは何か？

これは、[Hexメソッド]で[Colorプロパティ]を16進数に変換しました

セルの強調表示ルール 15

6桁で表現すると「06009C」となります

カラーコードの「9C0006」とは、赤と青の値が逆転しています

この差が、10進数とRGBの差です（10進数と16進数の差ではない）

VBAソースコードで[Colorプロパティ]を設定する際は、以下のように[RGBメソッド]を使用して、赤、緑、青を10進数で設定する方法をお勧めします！

    With Selection.FormatConditions(1).Font
        .Color = RGB(Red:=156, Green:=0, Blue:=6)   ' -16383844
        .TintAndShade = 0
    End With

説明は下手なので、理解して頂けたか、実に心配ですがｗｗｗ

ま、そんな感じです！

ではでは

広告

カテゴリー:条件付き書式

タグ:

やもす ʕ•͡-•ʔ

のんびり！のほほん！がモットーです！ｗ 蕎麦食いたい ライブ行きたい 暑いの嫌い

シェアする