【VBA】条件付き書式をVBAで一括設定・解除する方法（コピペOK）

この記事でできること

売上データで目標未達の数値を赤、達成を緑にしたい。手動で条件付き書式を設定しているが、毎月新しいシートにコピーすると書式が引き継がれない。毎月同じルールを手動で設定し直すのが面倒。しかもルールが増えすぎてExcelが重くなったことがある。

この記事のVBAを使えば、既存ルールの一括削除と新規ルールの一括設定がボタン1つで完了する。ルールの二重適用もExcelの重さも解消される。

利用シーン：

• 事務 — 月次レポートのシートを新規作成するたびに条件付き書式を手動設定している。VBAで一括設定すれば毎月の手間がゼロになる
• 品質管理 — 検査データの基準値超えを赤、ギリギリを黄、合格を緑に色分けしている。ルールの増殖によるExcelの重さも一括削除で解消
• 営業 — 売上目標の達成率に応じて案件リストを色分けしている。新しい期が始まるたびに同じルールを再設定する手間がなくなる

• 対象：条件付き書式を毎月手作業で設定している人。VBA初心者OK
• 所要時間：コピペ → 実行まで約5分

—

完成イメージ（Before / After）

Before（実行前） — 検査データが入ったExcelシート。条件付き書式は未設定で、基準値超えが一目でわからない。

After（実行後） — 基準値に応じて自動色分けされる。

B列のセルに条件付き書式が自動設定され、値に応じて背景色が変わる。「色」列は説明用です。実際のExcelではB列の背景色が直接変化します。

—

手順（コピペ → 実行まで約5分）

Excelファイルをマクロ有効ブックで保存する

1. Excelを開く（新規でも既存でもOK）
2. 「ファイル」→「名前を付けて保存」
3. ファイルの種類を 「Excel マクロ有効ブック (*.xlsm)」 に変更して保存

.xlsx のままだとマクロが保存されない。必ず .xlsm にすること。

VBE（コードを書く画面）を開く

Alt + F11 キーを押すとVBE（Visual Basic Editor）が開く。

一般的にはAlt + F11で開けるが、企業のセキュリティ設定でVBAが無効化されている場合は、IT部門に確認すること。

標準モジュールを挿入する

1. VBEのメニュー「挿入」→「標準モジュール」をクリック
2. 右側に白い画面（コードウィンドウ）が表示される

コードを貼り付けて実行する

1. 下の「コード（基本版）」をコピーして、コードウィンドウに貼り付ける
2. コード内の対象範囲や条件値を自分のデータに合わせて書き換える
3. Alt + F8 を押す（または VBE上で F5）
4. マクロ名を選択して「実行」
5. シートの条件付き書式が設定されていることを確認する

—

コード（基本版）— FormatConditions.Addで条件付き書式を設定

まずはこれだけで動く。指定範囲のセルに「値が100,000以上なら背景を緑にする」条件付き書式を設定する。

このコードは対象範囲の既存の条件付き書式をすべて削除してから設定します。既存ルールを残したい場合は FormatConditions.Delete の行を削除してください。

Sub SetConditionalFormatBasic()

    Dim ws As Worksheet
    Dim targetRange As Range

    Set ws = Worksheets("Sheet1")

    ' ★ 条件付き書式を設定する範囲を指定
    Set targetRange = ws.Range("B2:B20")

    ' 既存の条件付き書式を削除（二重設定を防ぐ）
    targetRange.FormatConditions.Delete

    ' 条件付き書式を追加：値が100000以上なら背景を緑にする
    With targetRange.FormatConditions.Add( _
        Type:=xlCellValue, _
        Operator:=xlGreaterEqual, _
        Formula1:="100000")

        .Interior.Color = RGB(198, 239, 206)  ' 薄い緑
        .Font.Color = RGB(0, 97, 0)           ' 濃い緑（文字色）
        .StopIfTrue = False
    End With

    MsgBox "条件付き書式を設定しました。", vbInformation

End Sub

ポイント：

• targetRange.FormatConditions.Delete で既存ルールを先に削除し、二重設定を防ぐ
• Type:=xlCellValue でセルの値を条件にする
• Operator:=xlGreaterEqual は「以上」。他に xlLess（未満）、xlEqual（等しい）、xlBetween（範囲内）などが使える
• Formula1:="100000" が閾値。自分のデータに合わせて書き換える
• .StopIfTrue = False で複数ルールを重ねて適用できる

条件付き書式を一括解除するだけのコード：

Sub DeleteConditionalFormatAll()
    ' ★ 対象範囲を指定（シート全体ならCellsを使う）
    Worksheets("Sheet1").Cells.FormatConditions.Delete
    MsgBox "条件付き書式をすべて解除しました。", vbInformation
End Sub

シート名について：コード内の "Sheet1" はシートのタブに表示される名前です。シート名を変更している場合は、自分のシート名に書き換えてください。

—

コード（応用版）— 複数条件・カラースケール・データバー

複数条件の設定

値に応じて3段階で色分けする。100,000以上は緑、70,000以上は黄、70,000未満は赤。

条件の設定順序が重要です。「100,000以上」を先に設定し、.StopIfTrue = False にすることで、すべてのルールが正しく評価されます。

Sub SetConditionalFormatMultiple()

    Dim ws As Worksheet
    Dim targetRange As Range

    Set ws = Worksheets("Sheet1")
    Set targetRange = ws.Range("B2:B20")

    ' 既存の条件付き書式を削除
    targetRange.FormatConditions.Delete

    ' 条件1：100,000以上 → 緑（最も優先度の高い条件を先に設定）
    With targetRange.FormatConditions.Add( _
        Type:=xlCellValue, _
        Operator:=xlGreaterEqual, _
        Formula1:="100000")

        .Interior.Color = RGB(198, 239, 206)
        .Font.Color = RGB(0, 97, 0)
        .StopIfTrue = False
    End With

    ' 条件2：70,000以上 → 黄
    With targetRange.FormatConditions.Add( _
        Type:=xlCellValue, _
        Operator:=xlGreaterEqual, _
        Formula1:="70000")

        .Interior.Color = RGB(255, 235, 156)
        .Font.Color = RGB(156, 101, 0)
        .StopIfTrue = False
    End With

    ' 条件3：70,000未満 → 赤
    With targetRange.FormatConditions.Add( _
        Type:=xlCellValue, _
        Operator:=xlLess, _
        Formula1:="70000")

        .Interior.Color = RGB(255, 199, 206)
        .Font.Color = RGB(156, 0, 6)
        .StopIfTrue = False
    End With

    MsgBox "3段階の条件付き書式を設定しました。", vbInformation

End Sub

ポイント：

• 条件は上から順に評価される。.StopIfTrue = False にしておくと、すべてのルールが評価される
• 3段階の色分けは「100,000以上（緑）→ 70,000以上（黄）→ それ以外（赤）」の順で設定する
• 順番を間違えると、たとえば150,000が「70,000以上」の黄にマッチしてしまう場合がある
• 閾値（100000, 70000）は自分のデータに合わせて変更する

カラースケールの設定

値の大小を色のグラデーションで表現する。最小値が赤、中間値が黄、最大値が緑になる。

Sub SetColorScale()

    Dim ws As Worksheet
    Dim targetRange As Range
    Dim cs As ColorScale

    Set ws = Worksheets("Sheet1")
    Set targetRange = ws.Range("B2:B20")

    ' 既存の条件付き書式を削除
    targetRange.FormatConditions.Delete

    ' 3色カラースケールを追加
    Set cs = targetRange.FormatConditions.AddColorScale(ColorScaleType:=3)

    ' 最小値 → 赤
    With cs.ColorScaleCriteria(1)
        .Type = xlConditionValueLowestValue
        .FormatColor.Color = RGB(248, 105, 107)
    End With

    ' 中間値 → 黄
    With cs.ColorScaleCriteria(2)
        .Type = xlConditionValuePercentile
        .Value = 50
        .FormatColor.Color = RGB(255, 235, 132)
    End With

    ' 最大値 → 緑
    With cs.ColorScaleCriteria(3)
        .Type = xlConditionValueHighestValue
        .FormatColor.Color = RGB(99, 190, 123)
    End With

    MsgBox "カラースケールを設定しました。", vbInformation

End Sub

データバーの設定

セル内に棒グラフを表示して、値の大小を視覚的に表現する。

Sub SetDataBar()

    Dim ws As Worksheet
    Dim targetRange As Range
    Dim db As Databar

    Set ws = Worksheets("Sheet1")
    Set targetRange = ws.Range("B2:B20")

    ' 既存の条件付き書式を削除
    targetRange.FormatConditions.Delete

    ' データバーを追加
    Set db = targetRange.FormatConditions.AddDatabar

    ' データバーの色を設定
    db.BarColor.Color = RGB(99, 142, 198)

    ' データバーの最小値・最大値を自動にする
    db.MinPoint.Modify newtype:=xlConditionValueAutomaticMin
    db.MaxPoint.Modify newtype:=xlConditionValueAutomaticMax

    ' セルの値も表示する（データバーだけにしない）
    db.ShowValue = True

    MsgBox "データバーを設定しました。", vbInformation

End Sub

ポイント：

• カラースケールは値の分布を色で表現する。ヒートマップのような見た目になる
• データバーはセル内に棒グラフが表示される。数値の大小が直感的にわかる
• ShowValue = True で数値とデータバーの両方を表示する

—

コード（実務版）— 売上データの自動色分け

実務で一番使うパターン。売上データの範囲を自動検出し、C列の目標値を基準に目標達成/警告/未達成の3段階で色分けする。既存ルールの削除から新規設定まで安全に一括処理する。

シートの準備：

B列に売上、C列に目標を入力しておくと、売上が目標以上なら緑、目標の70%以上なら黄、70%未満なら赤で自動色分けされる。

繰り返し実行する場合： マクロを再実行すると既存の条件付き書式は自動で削除してから再設定されるため、二重設定にはなりません。

Sub SetSalesConditionalFormat()

    Dim ws As Worksheet
    Dim lastRow As Long
    Dim targetRange As Range
    Dim i As Long

    Set ws = Worksheets("Sheet1")

    ' B列の最終行を取得
    lastRow = ws.Cells(ws.Rows.Count, 2).End(xlUp).Row

    ' データがない場合は終了
    If lastRow < 2 Then
        MsgBox "B列にデータがありません。", vbExclamation
        Exit Sub
    End If

    ' 対象範囲を設定（B2から最終行まで）
    Set targetRange = ws.Range("B2:B" & lastRow)

    ' 画面更新を一時停止（高速化）
    Application.ScreenUpdating = False

    ' 既存の条件付き書式を削除
    targetRange.FormatConditions.Delete

    ' 各行に対して、C列の目標値を基準に条件付き書式を設定
    For i = 2 To lastRow

        Dim cellRange As Range
        Dim targetValue As String

        Set cellRange = ws.Range("B" & i)

        ' C列に目標値がない場合はスキップ
        If ws.Cells(i, 3).Value = "" Then GoTo NextRow

        ' C列の値が数値でない場合もスキップ
        If Not IsNumeric(ws.Cells(i, 3).Value) Then GoTo NextRow

        targetValue = CStr(ws.Cells(i, 3).Value)

        ' 条件1：目標以上 → 緑（達成）
        With cellRange.FormatConditions.Add( _
            Type:=xlCellValue, _
            Operator:=xlGreaterEqual, _
            Formula1:=targetValue)

            .Interior.Color = RGB(198, 239, 206)
            .Font.Color = RGB(0, 97, 0)
            .Font.Bold = True
            .StopIfTrue = True
        End With

        ' 条件2：目標の70%以上 → 黄（警告）
        With cellRange.FormatConditions.Add( _
            Type:=xlCellValue, _
            Operator:=xlGreaterEqual, _
            Formula1:=CStr(CLng(CDbl(targetValue) * 0.7)))

            .Interior.Color = RGB(255, 235, 156)
            .Font.Color = RGB(156, 101, 0)
            .StopIfTrue = True
        End With

        ' 条件3：目標の70%未満 → 赤（未達成）
        With cellRange.FormatConditions.Add( _
            Type:=xlCellValue, _
            Operator:=xlLess, _
            Formula1:=CStr(CLng(CDbl(targetValue) * 0.7)))

            .Interior.Color = RGB(255, 199, 206)
            .Font.Color = RGB(156, 0, 6)
            .Font.Bold = True
            .StopIfTrue = True
        End With

NextRow:
    Next i

    ' 画面更新を再開
    Application.ScreenUpdating = True

    MsgBox lastRow - 1 & " 行の売上データに条件付き書式を設定しました。", vbInformation

End Sub

ポイント：

• B列の最終行を自動検出するため、データが増えても範囲を手動で変更する必要がない
• C列の目標値を基準にするため、商品ごとに異なる目標を設定できる
• IsNumeric チェックでC列に文字列が入っている場合もエラーにならない
• Application.ScreenUpdating = False で画面更新を止めて高速化
• 既存の条件付き書式を先に削除するため、繰り返し実行しても二重設定にならない
• .Font.Bold = True で達成・未達成を太字にして強調

条件付き書式の設定内容を一覧表示するコード：

Sub ListConditionalFormats()

    Dim ws As Worksheet
    Dim fc As FormatCondition
    Dim i As Long

    Set ws = Worksheets("Sheet1")

    If ws.Cells.FormatConditions.Count = 0 Then
        MsgBox "条件付き書式は設定されていません。", vbInformation
        Exit Sub
    End If

    Dim msg As String
    msg = "条件付き書式の一覧：" & vbCrLf & vbCrLf

    For i = 1 To ws.Cells.FormatConditions.Count
        On Error Resume Next
        Set fc = ws.Cells.FormatConditions(i)
        msg = msg & "ルール " & i & ": "
        msg = msg & "範囲=" & fc.AppliesTo.Address & ", "
        msg = msg & "条件=" & fc.Formula1
        msg = msg & vbCrLf
        On Error GoTo 0
    Next i

    MsgBox msg, vbInformation

End Sub

—

よくある落とし穴5選

条件付き書式が増えすぎてExcelが重いときの対処法

「Excelを開くだけで数十秒かかる」「セルを編集するたびにフリーズする」という場合、原因は条件付き書式のルールが大量に蓄積していることが多い。セルのコピペや行の挿入を繰り返すと、条件付き書式も一緒にコピーされて増殖する。「ルールの管理」を開くとスクロールが終わらないほどルールが溜まっていることがある。対処法は Cells.FormatConditions.Delete でシート全体のルールを一括削除すること。これだけでExcelの動作が劇的に軽くなる。自分の場合、30個以上溜まったルールを一括削除したら開くのに10秒かかっていたExcelが1秒で開くようになった。削除後に必要なルールだけVBAで再設定すれば、ルールの重複も防げる。

VBAのFormatConditions.Addで条件付き書式が設定できないときの対処法

「FormatConditions.Add を実行したのに色が変わらない」という場合、原因は Operator や Formula1 の指定ミスが多い。たとえば「100以上」を指定するつもりで Operator:=xlGreater と書くと「100より大きい（100を含まない）」になる。「100以上」は Operator:=xlGreaterEqual が正しい。また、Formula1 に数値を渡す場合は文字列型 "100" で渡す必要がある。数値型のまま渡すと環境によってエラーになることがある。自分も xlGreater と xlGreaterEqual を間違えて「100万ちょうどの売上だけ色が付かない」というバグを出したことがある。

VBAで条件付き書式を削除したのにルールが消えないときの対処法

「FormatConditions.Delete を実行したのにルールがまだ残っている」という場合、原因は削除範囲が意図したものと異なっていることだ。Range("B2:B20").FormatConditions.Delete ではB2:B20に適用されているルールしか消えない。シート全体のルールを消すには Cells.FormatConditions.Delete または ActiveSheet.Cells.FormatConditions.Delete を使う。また、複数シートにルールが溜まっている場合は、全シートをループして各シートで Cells.FormatConditions.Delete を実行する。複数シートを一括処理する方法 のループパターンを使えば全シート一括で掃除できる。

—

FAQ

Q1: 既存の条件付き書式を全部消してからやり直したい

Cells.FormatConditions.Delete でシート全体の条件付き書式を一括削除できます。特定の範囲だけ削除したい場合は Range("B2:B20").FormatConditions.Delete のように範囲を指定してください。

Q2: 条件付き書式とVBAのInterior.Colorの違いは？

条件付き書式はセルの値が変わると自動で書式が更新されます。VBAの Interior.Color は実行時点の色を直接設定するため、値が変わっても色は変わりません。動的に色を変えたい場合は条件付き書式が適しています。セルの値に応じた行の色分けについては セルの値に応じて行を自動色分けする方法 も参考にしてください。

Q3: カラースケールとデータバーはどう使い分ける？

カラースケールはセルの背景色をグラデーションで表示し、全体の分布を把握するのに向いています。データバーはセル内に棒グラフが表示され、個々の値の大小を比較するのに向いています。

Q4: 条件付き書式の優先順位を変えたい

VBAでは FormatConditions の追加順で優先順位が決まります。後から追加したルールが優先されます。特定のルールを最優先にしたい場合は .SetFirstPriority メソッドを使ってください。

Q5: 特定の文字を含むセルに条件付き書式を設定したい

Type:=xlTextString を使うか、Type:=xlExpression で SEARCH 関数を使います。特定の文字を含むセルの検索・ハイライトについては 特定の文字を含むセルを検索してハイライトする方法 も参考にしてください。

—

まとめ

この記事で、VBAを使って条件付き書式の一括設定・解除ができるようになりました。

• 基本版：FormatConditions.Add で1つの条件付き書式を設定
• 応用版：複数条件の色分け、カラースケール、データバーを設定
• 実務版：売上データをC列の目標値を基準に3段階で自動色分け
• 一括削除：Cells.FormatConditions.Delete で溜まったルールを一掃

条件付き書式のルールが増えすぎてExcelが重くなったら、まず一括削除で掃除してから、VBAで必要なルールだけ再設定するのが一番確実だ。自分はこの運用にしてから、「ルールの管理」画面を開く必要がなくなった。マクロを実行すれば常に最新の正しいルールだけが適用される。手動で設定していたときの「先月のルールが残っていて二重適用」「コピペでルールが増殖」という問題が完全になくなった。

セルの値に応じて行を自動色分けする方法は セルの値に応じて行を自動色分けする方法 で解説しています。また、セルの書式を一括変更する方法は セルの書式を一括変更する方法 を参考にしてください。

関連記事

• セルの値に応じて行を自動色分けする方法 — Interior.Colorで直接色を付ける方法との使い分け
• 特定の文字を含むセルを検索してハイライトする方法 — 文字列条件でセルを強調表示
• セルの書式を一括変更する方法 — 条件付き書式ではなく通常の書式を一括変更
• セルの背景色・文字色をRGBで自由に操作する方法 — RGB指定での色操作を詳しく知りたい場合
• 複数シートを一括処理する方法 — 複数シートに同じ条件付き書式をループで設定

—

もっとカスタマイズしたい場合

「部門ごとに異なる色分けルールを設定したい」「既存の条件付き書式を整理して最適化したい」「月次レポートの書式設定を完全自動化したい」など、業務に合わせたカスタマイズが必要な場合は、ココナラで相談できます。

相談時に以下の情報があるとスムーズです：

• Excel のバージョン / OS
• 色分けしたいデータの種類と条件（例：売上、在庫、期限など）
• 対象シート数と範囲
• 現在の条件付き書式の状況（ルールが多すぎて重い等）