Excel VBA 逆引き集 | 条件付き書式をVBAで適用

Excel VBA
スポンサーリンク

条件付き書式をVBAで適用

条件付き書式は「値に応じてセルの見た目を自動で切り替える」仕組み。VBAでは Range.FormatConditions にルールを追加して、色やフォント、アイコン、データバーなどを設定します。最短コードから、式ルール・セル値ルール・色階調・データバー・アイコンセット、そしてルール管理の鉄則までを初心者向けに整理します。

基本構造と使い分け

  • 基本構造: Range.FormatConditions.Add(…) でルール追加 → 返ってきた FormatCondition/DataBar/IconSetCondition に書式を設定。
  • 主要タイプ:
    • 式ルール: Type:=xlExpression、Formula1 に数式（相対参照に強い）。
    • セル値ルール: Type:=xlCellValue、Operator と値で条件（>=、Between など）。
    • 色スケール・データバー・アイコン: AddColorScale / AddDatabar / AddIconSetCondition を使用。
  • 削除・優先度: FormatConditions.Delete で全削除、SetFirstPriority、StopIfTrue で優先制御。

式ルール（最も柔軟）

Sub CF_Expression_Basic()
    Dim rng As Range
    Set rng = Range("B3:E100")
    rng.FormatConditions.Delete
    ' 行ごとに「F列が×」なら灰色アウト
    With rng.FormatConditions.Add(Type:=xlExpression, Formula1:="=$F3=""×""")
        .Interior.Color = RGB(217, 217, 217)
        .Font.Color = RGB(128, 128, 128)
    End With
End Sub
VB
  • ポイント:
    • 相対参照: 数式は適用先の左上セル基準。行に対して F列を固定するなら「$F3」のように列固定・行相対にする。
    • 式ルールは最強: 他列の値参照、土日判定、重複判定など柔軟に書ける。

セル値ルール（しきい値が明確なとき）

Sub CF_CellValue_Threshold()
    Dim rng As Range, fc As FormatCondition
    Set rng = Range("E3:E100")
    rng.FormatConditions.Delete
    Set fc = rng.FormatConditions.Add(Type:=xlCellValue, Operator:=xlGreaterEqual, Formula1:="1000000")
    fc.Interior.Color = RGB(198, 239, 206) '100万以上を淡い緑
End Sub
VB
  • ポイント:
    • Operator: xlEqual、xlNotEqual、xlGreater、xlLess、xlBetween などを選ぶ。
    • Formula1/2: Between のときは下限・上限を使い分け。

色スケール（グラデーションで大小を可視化）

Sub CF_ColorScale_3Color()
    Dim cs As ColorScale
    Set cs = Range("E3:E100").FormatConditions.AddColorScale(ColorScaleType:=3)
    ' 最小＝赤、中間＝黄、最大＝緑
    With cs.ColorScaleCriteria(1)
        .Type = xlConditionValueLowestValue
        .FormatColor.Color = RGB(255, 0, 0)
    End With
    With cs.ColorScaleCriteria(2)
        .Type = xlConditionValuePercentile
        .Value = 50
        .FormatColor.Color = RGB(255, 255, 0)
    End With
    With cs.ColorScaleCriteria(3)
        .Type = xlConditionValueHighestValue
        .FormatColor.Color = RGB(0, 176, 80)
    End With
End Sub
VB
  • ポイント:
    • ColorScale: 2色/3色で連続値の差を視覚化。パーセンタイルで外れ値に強くできる。

データバー（棒で量を見せる）

Sub CF_DataBar_Basic()
    Dim db As DataBar
    Set db = Range("F3:F100").FormatConditions.AddDatabar
    With db
        .BarColor.Color = RGB(0, 112, 192)
        .MinPoint.Modify xlConditionValueAutomaticMin
        .MaxPoint.Modify xlConditionValueAutomaticMax
        .AxisPosition = xlDataBarAxisAutomatic
        .BarBorder.Type = xlDataBarBorderSolid
    End With
End Sub
VB
  • ポイント:
    • Min/Max自動: 範囲の最小・最大でバー長を決定。固定したいなら数値やパーセンタイル指定に切替。
    • 負値対応: AxisPosition と NegativeBarFormat で軸・色を調整。

アイコンセット（記号で三段階など）

Sub CF_IconSet_Custom()
    Dim ic As IconSetCondition
    Set ic = Range("G3:G100").FormatConditions.AddIconSetCondition
    With ic
        .IconSet = ActiveWorkbook.IconSets(xl3TrafficLights2)
        .ShowIconOnly = False
        ' 70%/90%で区切る
        With .IconCriteria(2)
            .Type = xlConditionValueNumber
            .Value = 0.7
        End With
        With .IconCriteria(3)
            .Type = xlConditionValueNumber
            .Value = 0.9
        End With
    End With
End Sub
VB
  • ポイント:
    • IconCriteria: 3段階セットは2番目・3番目境界を設定。Type は数値/パーセント/パーセンタイルから選択。
    • ReverseOrder: 高い値＝良アイコンが基本。逆にしたい時は True。

よくある用途テンプレート

Sub CF_DueDate_Overdue()
    Dim rng As Range
    Set rng = Range("D3:D100")
    rng.FormatConditions.Delete
    ' 期限が今日より前で未完了なら赤（隣列Eが「未」）
    With rng.FormatConditions.Add(Type:=xlExpression, Formula1:="=AND(D3<TODAY(),$E3=""未"")")
        .Interior.Color = RGB(255, 199, 206)
        .Font.Color = RGB(192, 0, 0)
    End With
End Sub

Sub CF_Top10_Highlight()
    Dim rng As Range
    Set rng = Range("E3:E100")
    rng.FormatConditions.Delete
    rng.FormatConditions.AddTop10
    With rng.FormatConditions(rng.FormatConditions.Count)
        .TopBottom = xlTop10Top
        .Rank = 10
        .Interior.Color = RGB(255, 235, 156)
    End With
End Sub

Sub CF_Duplicates_Highlight()
    Dim rng As Range
    Set rng = Range("B3:B200")
    rng.FormatConditions.Delete
    rng.FormatConditions.AddUniqueValues
    With rng.FormatConditions(rng.FormatConditions.Count)
        .DupeUnique = xlDuplicate
        .Interior.Color = RGB(255, 199, 206)
    End With
End Sub
VB
  • ポイント:
    • 日付判定: TODAY(), AND() などの式で柔軟に実現。
    • 上位・下位 N: AddTop10 の Rank と TopBottom で簡単指定。
    • 重複: AddUniqueValues の DupeUnique で重複/一意の強調。

ルール管理の鉄則（削除・優先度・停止）

Sub CF_ResetAndOrder()
    Dim rng As Range, fc1 As FormatCondition, fc2 As FormatCondition
    Set rng = Range("B3:E100")
    rng.FormatConditions.Delete                 'まず既存ルールを消す

    'ルール1：遅延赤
    Set fc1 = rng.FormatConditions.Add(Type:=xlExpression, Formula1:="=$E3=""遅延""")
    fc1.Interior.Color = RGB(255, 199, 206)

    'ルール2：完了灰
    Set fc2 = rng.FormatConditions.Add(Type:=xlExpression, Formula1:="=$E3=""完了""")
    fc2.Interior.Color = RGB(217, 217, 217)

    fc1.SetFirstPriority                        '赤を最優先
    fc1.StopIfTrue = True                       '赤が当たったら以降停止
End Sub
VB
  • ポイント:
    • Delete→再適用: 意図しない重複を避けるために再設定前に削除が安全。
    • 優先度制御: SetFirstPriority と StopIfTrue で衝突回避。

例題で練習

例題1：売上にデータバー、達成率に三色アイコン、既存ルールはクリア

Sub Example_SalesAndRate()
    Dim rngS As Range, rngR As Range
    Set rngS = Range("E3:E100")
    Set rngR = Range("F3:F100")
    rngS.FormatConditions.Delete
    rngR.FormatConditions.Delete

    Dim db As DataBar: Set db = rngS.FormatConditions.AddDatabar
    db.BarColor.Color = RGB(0, 176, 80)
    db.MinPoint.Modify xlConditionValueNumber, 0
    db.MaxPoint.Modify xlConditionValueNumber, 1000000

    Dim ic As IconSetCondition: Set ic = rngR.FormatConditions.AddIconSetCondition
    ic.IconSet = ActiveWorkbook.IconSets(xl3TrafficLights1)
    With ic.IconCriteria(2): .Type = xlConditionValueNumber: .Value = 0.8: End With
    With ic.IconCriteria(3): .Type = xlConditionValueNumber: .Value = 0.95: End With
End Sub
VB

例題2：プロジェクト行を色分け（完了=灰、遅延=赤、予定=黄）

Sub Example_ProjectStatus()
    Dim rng As Range: Set rng = Range("B3:H100")
    rng.FormatConditions.Delete
    Dim fc As FormatCondition

    Set fc = rng.FormatConditions.Add(Type:=xlExpression, Formula1:="=$G3=""完了""")
    fc.Interior.Color = RGB(217, 217, 217): fc.StopIfTrue = True: fc.SetFirstPriority

    Set fc = rng.FormatConditions.Add(Type:=xlExpression, Formula1:="=$G3=""遅延""")
    fc.Interior.Color = RGB(255, 199, 206): fc.StopIfTrue = True

    Set fc = rng.FormatConditions.Add(Type:=xlExpression, Formula1:="=$G3=""予定""")
    fc.Interior.Color = RGB(255, 235, 156)
End Sub
VB

例題3：週末を薄いグレーに（カレンダー列）

Sub Example_WeekendShade()
    Dim rng As Range: Set rng = Range("C3:C100")
    rng.FormatConditions.Delete
    With rng.FormatConditions.Add(Type:=xlExpression, Formula1:="=WEEKDAY(C3,2)>=6")
        .Interior.Color = RGB(242, 242, 242)
    End With
End Sub
VB

実務の落とし穴と対策

  • 参照のずれ: 式ルールは適用範囲の左上基準。$で列/行の固定を正しく設計。
  • 外れ値で見え方崩壊: データバーや色スケールは Min/Max を固定値やパーセンタイルにすると安定。
  • ルール肥大化: 追加のたびに増える。再適用前に FormatConditions.Delete が鉄則。
  • 優先度衝突: SetFirstPriority と StopIfTrue を活用して「勝ち負け」を明確に。
  • テーブルとの混在: テーブルスタイルと条件付き書式が重なると過剰演出になりがち。どちらを主役にするか決める。
スポンサーリンク
タイトルとURLをコピーしました