Excel VBAで条件付き書式を使う

Excelの条件付き書式設定

Excelの条件付き書式を使用すると、セルの書式を決定するルールを定義することができます。 例えば、特定の条件を満たすセルを強調表示するルールを作成できます。例としては、以下のようなものがあります。

• ある範囲に入る数字（例：0未満）
• リスト内の上位10項目
• ヒートマップの作成
• ほぼすべての条件付き書式に対応した “数式ベース “のルール

Excelでは、リボンの「ホーム」→「スタイル」（ALT→H→L）に「条件付き書式」が表示されます。

独自のルールを作成するには、「新しいルール」をクリックすると、新しいウィンドウが表示されます。

VBAでの条件付き書式設定

これらの条件付き書式の機能は、すべてVBAを使用してアクセスできます。 VBAコードから条件付き書式を設定すると、新しいパラメータがExcelのフロントエンドの条件付き書式ウィンドウに表示され、ユーザーが見ることができるようになることに注意してください。ユーザーは、ワークシートをロックしていない限り、これらを編集または削除することができます。

条件付き書式の規則は、ワークシートが保存されたときにも保存されます。

条件付き書式規則は、特定のワークシートとセルの特定の範囲にのみ適用されます。 ワークブック内の他の場所で必要な場合は、そのワークシートでも設定する必要があります。

VBAでの条件付き書式の実用的な例

CSV（カンマ区切り値）ファイルやデータベースのテーブル、クエリから、大量の生データをワークシートにインポートすることがあります。 これは、ダッシュボードやレポートに流れ込み、ある期間から別の期間へとインポートされた数値が変化する可能性があります。

数値が変化し、許容範囲外になった場合、セルの背景色を赤にするなどして強調したい場合があります。このようにすると、ユーザーは即座にこの数字に注目し、なぜこのようなことが起こるのかを調査することができます。

VBAを使って、条件付き書式のオン・オフを切り替えることができます。VBAを使って、セル範囲のルールをクリアしたり、再びオンにしたりすることができます。異常な数値には十分な理由があるが、ユーザーがダッシュボードやレポートをより高いレベルの管理者に見せるとき、「警鐘」を取り除くことができるようにしたい、という状況があるかもしれません。

また、インポートした生のデータ上で、数値が異常に大きい、または異常に小さい箇所を強調したい場合もあります。インポートされたデータ範囲は通常、期間ごとに異なるサイズなので、VBAを使って新しいデータ範囲のサイズを評価し、その範囲にのみ条件付き書式を挿入することができるのです。

また、従業員の給与や試験の点数など、名前のソートされたリストがあり、それぞれに対して数値が設定されている場合もあります。条件付き書式を使用すると、最高値から最低値まで段階的な色を使用することができ、プレゼンテーションのために非常に印象的に見えます。

しかし、名前のリストのサイズは常に一定ではないので、VBAコードを使用して、範囲のサイズの変更に応じて段階的に色のスケールを更新することになるでしょう。

範囲に条件付き書式を作成する簡単な例

この例では、ワークシートのセル範囲（A1:A10）に対して条件付き書式を設定しています。 範囲内の数値が100から150の場合は、セルの背景色が赤になり、それ以外の場合は色が付きません。

Sub ConditionalFormattingExample()

'範囲を定義する
Dim MyRange As Range
Set MyRange = Range("A1:A10")

'Rangeから既存の条件付き書式を削除する
MyRange.FormatConditions.Delete

'条件付き書式を適用
MyRange.FormatConditions.Add Type:=xlCellValue, Operator:=xlBetween, _
        Formula1:="=100", Formula2:="=150"
MyRange.FormatConditions(1).Interior.Color = RGB(255, 0, 0)
End Sub

まず、条件付き書式を適用するための範囲MyRangeを定義していることに注目してください。 次に、その範囲に存在する条件付き書式をすべて削除します。これは、コードを実行するたびに同じルールが追加されるのを防ぐための良いアイデアです（もちろん、すべての状況において適切であるとは限りません）。 色は数値で指定します。 これにはRGB（Red, Green, Blue）表記を使うとよいでしょう。 標準的な色定数、例えばvbRedやvbBlueを使用することもできますが、色の選択肢は8色に限られます。 1670万色以上の色があり、RGBを使えばそのすべてにアクセスすることができます。 どの番号がどの色に対応するかを覚えるより、はるかに簡単です。RGBの3つの色番号はそれぞれ0から255までです。 xlBetweenパラメーターは包括的なので、セルの値が100や150でも条件を満たせることに注意してください。

複数条件の書式設定

データ範囲内に複数の条件ルールを設定し、範囲内のすべての値が異なる条件でカバーされるようにすることもできます。

Sub MultipleConditionalFormattingExample()
Dim MyRange As Range
'範囲オブジェクトを作成する
Set MyRange = Range("A1:A10")
'以前の条件付き書式を削除する
MyRange.FormatConditions.Delete
'1つ目のルールを追加
MyRange.FormatConditions.Add Type:=xlCellValue, Operator:=xlBetween, _
        Formula1:="=100", Formula2:="=150"
MyRange.FormatConditions(1).Interior.Color=RGB(255, 0, 0)
'2つ目のルールを追加
MyRange.FormatConditions.Add Type:=xlCellValue, Operator:=xlLess, _
        Formula1:="=100"
MyRange.FormatConditions(2).Interior.Color=vbBlue
'3つ目のルールを追加
MyRange.FormatConditions.Add Type:=xlCellValue, Operator:=xlGreater, _
        Formula1:="=150"
MyRange.FormatConditions(3).Interior.Color=vbYellow
End Sub

この例では、前と同様に最初のルールを設定し、セル値が100から150の間にある場合、セルの色を赤にしています。 次に、2つのルールを追加しています。セルの値が 100 より小さければ、セルの色は青に、150 より大きければ、セルの色は黄色になります。 この例では、数字のすべての可能性をカバーし、ルールが重複しないようにする必要があります。 空白のセルがこの範囲にある場合、Excelはそのセルを100未満の値であるとみなすため、青色で表示されます。 これを回避するには、別の条件を式として追加することです。この条件は、コード内の最初の条件ルールとして追加する必要があります。複数のルールがある場合、実行順序を正しくしないと結果が予測できなくなる可能性があるため、非常に重要なことです。

Sub MultipleConditionalFormattingExample()
Dim MyRange As Range
'Rangeオブジェクトを作成する
Set MyRange = Range("A1:A10")
'以前の条件付き書式を削除する
MyRange.FormatConditions.Delete
'1つ目のルールを追加
MyRange.FormatConditions.Add Type:=xlExpression, Formula1:= _
        "=LEN(TRIM(A1))=0"
MyRange.FormatConditions(1).Interior.Pattern=xlNone
'2つ目のルールを追加
MyRange.FormatConditions.Add Type:=xlCellValue, Operator:=xlBetween, _ 
        Formula1:="=100", Formula2:="=150"
MyRange.FormatConditions(2).Interior.Color=RGB(255, 0, 0)
3つ目のルールを追加
MyRange.FormatConditions.Add Type:=xlCellValue, Operator:=xlLess, _
        Formula1:="=100"
MyRange.FormatConditions(3).Interior.Color=vbBlue
'4つ目のルールを追加
MyRange.FormatConditions.Add Type:=xlCellValue, Operator:=xlGreater, _
        Formula1:="=150"
MyRange.FormatConditions(4).Interior.Color = RGB(0, 255, 0)
End Sub

これは、xlExpressionの型を使用し、数値の代わりにセルが空白かどうかを判断するために、Excelの標準的な数式を使用しています。 FormatConditionsオブジェクトは、Rangeオブジェクトの一部です。このオブジェクトは、インデックスが1から始まるコレクションと同じように動作します。 このオブジェクトは、For…Next または For…Each ループを使って反復処理することができます。

ルールの削除

複数のルールの集合の中で、データ要件に合わないルールを個別に削除する必要がある場合があります。

Sub DeleteConditionalFormattingExample()
Dim MyRange As Range
'範囲オブジェクトを作成する
Set MyRange = Range("A1:A10")
'以前の条件付き書式を削除する
MyRange.FormatConditions.Delete
'最初の規則を追加する
MyRange.FormatConditions.Add Type:=xlCellValue, Operator:=xlBetween, _
        Formula1:="=100", Formula2:="=150"
MyRange.FormatConditions(1).Interior.Color = RGB(255, 0, 0)
'ルールを削除する
MyRange.FormatConditions(1).Delete
End Sub

このコードでは、範囲A1:A10に対して新しいルールを作成し、それを削除しています。 削除には正しいインデックス番号を使用しなければなりませんので、Excelのフロントエンドの「ルールの管理」（実行順にルールが表示されます）で、正しいインデックス番号を取得しているかどうか確認してください。 VBAで条件付きフォーマットのルールを削除した場合、Excelのフロントエンドから削除した場合とは異なり、Excelには元に戻す機能がない（Undoできない）ことに注意してください。

ルールの変更

ルールは指定した範囲に基づくオブジェクトの集まりなので、VBAを使用して特定のルールに簡単に変更を加えることができます。 ルールが追加された後の実際のプロパティは読み取り専用ですが、Modifyメソッドを使用して変更することができます。色などのプロパティは、読み取り/書き込みが可能です。

Sub ChangeConditionalFormattingExample()
Dim MyRange As Range
'レンジオブジェクトの作成
Set MyRange = Range("A1:A10")
'以前の条件付き書式を削除
MyRange.FormatConditions.Delete
'1つ目のルールを追加する
MyRange.FormatConditions.Add Type:=xlCellValue, Operator:=xlBetween, _
        Formula1:="=100", Formula2:="=150"
MyRange.FormatConditions(1).Interior.Color = RGB(255, 0, 0)
'ルールを変更する
MyRange.FormatConditions(1).Modify xlCellValue, xlLess, "10"
'ルールの色を変更する
MyRange.FormatConditions(1).Interior.Color=vbGreen
End Sub

このコードでは、範囲オブジェクト（A1:A10）を作成し、100から150までの数値に対するルールを追加しています。 条件が真であれば、セルの色は赤に変わります。 次に、このコードは10未満の数値のルールを変更します。この条件が満たされると、セルの色は緑に変わります。

段階的な配色の使用

Excelの条件付き書式では、昇順や降順で並んだ数値に段階的な色をつけることができます。 これは、地域別の売上高、都市の気温、都市間の距離などのデータがある場合に、非常に便利です。VBAを使用すると、Excelのフロントエンドで提供される標準の色ではなく、独自の目盛り付きカラースキームを選択できるという利点もあります。

Sub GraduatedColors()
Dim MyRange As Range
'範囲オブジェクトを作成する
Set MyRange = Range("A1:A10")
'以前の条件付き書式を削除する
MyRange.FormatConditions.Delete
'スケールの種類を定義
    MyRange.FormatConditions.AddColorScale ColorScaleType:=3
    '範囲の最小値の色を選択する
    MyRange.FormatConditions(1).ColorScaleCriteria(1).Type = _
        xlConditionValueLowestValue
    With MyRange.FormatConditions(1).ColorScaleCriteria(1).FormatColor
        .Color = 7039480
    End With
    '範囲の中間値を設定する
    MyRange.FormatConditions(1).ColorScaleCriteria(2).Type = _
        xlConditionValuePercentile
    MyRange.FormatConditions(1).ColorScaleCriteria(2).Value = 50
    '範囲の中間値の色を選択する
    With MyRange.FormatConditions(1).ColorScaleCriteria(2).FormatColor
        .Color = 8711167
    End With
     '範囲の最大値の色を選択する
    MyRange.FormatConditions(1).ColorScaleCriteria(3).Type = _
        xlConditionValueHighestValue
    With MyRange.FormatConditions(1).ColorScaleCriteria(3).FormatColor
        .Color = 8109667
    End With
End Sub

このコードを実行すると、範囲A1:A10の昇順の値に従ってセルの色を卒業させます。

これは非常に印象的なデータの表示方法であり、ユーザーの注意を引くことは間違いないでしょう。

エラー値に対する条件付き書式設定

膨大な量のデータがある場合、様々なワークシートでエラー値を見逃すことがあります。 これが解決されないままユーザーに提示されると、大きな問題に発展したり、ユーザーが数値に自信を失ったりする可能性があります。 これは、ルールタイプのxlExpressionとExcel関数のIsErrorを使って、セルを評価するものです。 エラーが入っているセルは全てセルの色が赤になるようにコードを作成します。

Sub ErrorConditionalFormattingExample()
Dim MyRange As Range
'範囲オブジェクトを作成する
Set MyRange = Range("A1:A10")
'以前の条件付き書式を削除する
MyRange.FormatConditions.Delete
'エラー用のルールを追加する
MyRange.FormatConditions.Add Type:=xlExpression, Formula1:="=IsError(A1)=true"
'エラーとなるセルの色を赤に設定する
MyRange.FormatConditions(1).Interior.Color=RGB(255, 0, 0)
End Sub

過去の日付の条件付き書式設定

インポートしたデータの中で、過去の日付を強調表示したい場合があります。 例えば、30日以上前の古い請求書を目立たせたい債務者レポートなどが考えられます。 このコードでは、ルールタイプのxlExpressionとExcel関数を使用して、日付を評価します。

Sub DateInPastConditionalFormattingExample()
Dim MyRange As Range
'日付の列に基づいて範囲オブジェクトを作成する
Set MyRange = Range("A1:A10")
'以前の条件付き書式を削除する
MyRange.FormatConditions.Delete
'過去の日付に対するエラールールを追加する
MyRange.FormatConditions.Add Type:=xlExpression、Formula1:="=Now()-A1 > 30"
'エラーとなるセルの色を赤に設定する
MyRange.FormatConditions(1).Interior.Color=RGB(255、0、0)
End Sub

このコードは、A1:A10の範囲で日付を取得し、過去30日以上経過した日付のセルの色を赤に設定するものです。 条件となる数式では、Now()が現在の日時を表しています。 これは、ワークシートが再計算されるたびに再計算され続けるので、書式設定は1日ごとに変更されます。

VBAの条件付き書式設定でデータバーを使う

VBAを使用して数値の範囲にデータバーを追加することができます。 これはほとんどミニ・チャートのようなもので、数値の大きさが互いに関連していることを即座に確認することができます。 データバーにはデフォルト値を使用することで、コードは非常に簡単に書くことができます。

Sub DataBarFormattingExample()
Dim MyRange As Range
Set MyRange = Range("A1:A10")
MyRange.FormatConditions.Delete
MyRange.FormatConditions.AddDatabar
End Sub

ワークシート上では、このようにデータが表示されます。

VBAの条件付き書式でアイコンを使う

条件付き書式を使用すると、ワークシートの数値の横にアイコンを置くことができます。 アイコンは、矢印や円など、さまざまな形状にすることができます。 この例では、パーセント値に基づいて数値に矢印のアイコンを追加するコードです。

Sub IconSetsExample()
Dim MyRange As Range
'Rangeオブジェクトを作成する
Set MyRange = Range("A1:A10")
'以前の条件付き書式を削除する
MyRange.FormatConditions.Delete
'FormatConditionsオブジェクトにIcon Setを追加する
MyRange.FormatConditions.AddIconSetCondition
'アイコンセットを矢印に設定 - 条件1
With MyRange.FormatConditions(1)
    .IconSet = ActiveWorkbook.IconSets(xl3Arrows)
End With
'必要なパーセント値のアイコン条件を設定する - 条件2
With MyRange.FormatConditions(1).IconCriteria(2)
    .Type = xlConditionValuePercent
    .Value = 33
    .Operator = xlGreaterEqual
End With
'必要なパーセント値のアイコン基準を設定する - 条件3
With MyRange.FormatConditions(1).IconCriteria(3)
    .Type = xlConditionValuePercent
    .Value = 67
    .Operator = xlGreaterEqual
End With
End Sub

これで、数値が高いか低いかが一目瞭然になります。 このコードを実行すると、ワークシートは次のようになります。

条件付き書式で上位5つをハイライトする

VBAコードを使用して、データ範囲内の上位5つの数字をハイライトすることができます。 AddTop10というパラメータを使用しますが、コード内で順位番号を5に調整することができます。 ユーザーは、最初にデータをソートすることなく、範囲の中で最も高い数字を見ることが出来るようになります。

Sub Top5Example()
Dim MyRange As Range
'Rangeオブジェクトを作成する
Set MyRange = Range("A1:A10")
'以前の条件付き書式を削除する
MyRange.FormatConditions.Delete
    'Top10条件を追加
    MyRange.FormatConditions.AddTop10
    With MyRange.FormatConditions(1)
        'トップからボトムまでのパラメータを設定
        .TopBottom = xlTop10Top
        '上位5つだけを設定
        .Rank = 5
    End With
    With MyRange.FormatConditions(1).Font
        'フォント色を設定
        .Color = -16383844
    End With
    With MyRange.FormatConditions(1).Interior
        'セルの背景の色を設定
        .Color = 13551615
    End With
End Sub

このコードを実行すると、ワークシートのデータは次のようになります。

145という値が2回表示されるので、6つのセルがハイライトされていることに注意してください。

StopIfTrue パラメータと SetFirstPriority パラメータの意義

StopIfTrueは、セル範囲に複数の条件付き書式規則がある場合に重要です。 範囲内の1つのセルが最初のルールを満たす場合もありますが、それ以降のルールを満たす場合もあります。開発者としては、最初に来たルールに対してのみ書式を表示させたいと思うかもしれません。 他のルールの基準は重複する可能性があり、ルールのリストの下に続けることを許可すると、意図しない変更を行う可能性があります。 このパラメータのデフォルトはTrueですが、そのセルの他のすべてのルールを考慮したい場合は、変更することができます。

MyRange.MyRange.FormatConditions(1).StopIfTrue = False

SetFirstPriority パラメータは、そのセルに対して複数のルールがある場合に、その条件ルールが最初に評価されるかどうかを指定します。

MyRange.FormatConditions(1).SetFirstPriority

これにより、そのルールの位置は、フォーマット条件のコレクション内の位置 1 に移動し、他のルールは、変更されたインデックス番号で下方に移動します。インデックス番号を使用してコード内のルールに変更を加える場合は、注意が必要です。正しいルールを変更または削除していることを確認する必要があります。

以下のように、ルールの優先順位を直接変更することもできます。

MyRange.FormatConditions(1).Priority=3

これにより、条件付き書式リスト内の他のルールの相対的な位置が変更されます。

他のセルの値を参照して条件付き書式を使う

これは、Excelの条件付き書式ができないことの一つです。しかし、これを実現するためのVBAコードを自作することができます。 データの列があり、各数字に隣接するセルに、各数字にどのような書式を適用するかを示すテキストがあるとします。 次のコードは、数値のリストを実行し、隣接するセルで書式設定テキストを探し、必要に応じて数値を書式設定します。

Sub ReferToAnotherCellForConditionalFormatting()
'表形式データの行数を保持する変数を作成する
Dim RRow As Long, N As Long
'表形式データの範囲内の行数を取得する
RRow = ActiveSheet.UsedRange.Rows.Count
'表形式データ範囲内のすべての行を繰り返し処理する
For N = 1 To RRow
    'Select Caseステートメントを使用して、列2に基づく書式設定を評価する
    Select Case ActiveSheet.Cells(N, 2).Value
        'セルの背景色を青にする
        Case "Blue"
        ActiveSheet.Cells(N, 1).Interior.Color = vbBlue
        'セルの背景色を赤にする
        Case "Red"
        ActiveSheet.Cells(N, 1).Interior.Color = vbRed
        'セルの背景色を緑にする
        Case "Green"
        ActiveSheet.Cells(N, 1).Interior.Color = vbGreen
    End Select
Next N
End Sub

このコードが実行されると、ワークシートは次のようになります。

書式設定のために参照されるセルは、ワークシート上の任意の場所、またはワークブック内の別のワークシートにすることができます。書式設定の条件となるテキストはどのようなものでも構いませんし、このコードの用途はあなたの想像力によってのみ制限されます。

条件付き書式設定ステートメントで使用できる演算子

これまでの例で見てきたように、演算子は条件の値がどのように評価されるかを決定するために使用されます。 これらの演算子は、ルールの条件をどのように指定するかによって、いくつも使い分けることができます。

演算子名	値	説明
xlBetween	1	次の値との間。2つの数式が提供されている場合のみ使用可能。
xlEqual	3	次の値に等しい。
xlGreater	5	次の値より大きい。
xlGreaterEqual	7	次の値以上。
xlLess	6	次の値より小さい。
xlLessEqual	8	次の値以下。
xlNotBetween	2	次の値との間以外。2つの数式が提供される場合のみ使用可能。
xlNotEqual	4	次の値に等しくない。