Excel VBA の条件付き書式 — 古びるループではなく、生き続けるルール

TL;DR — 条件付き書式のルールは、Excel に一度与える 常駐する指示 です。Excel は編集のたびに再実行します。For Each ... Interior.Color のループは写真です — 走った瞬間は正しく、値が変わった瞬間に古びます。ルールは範囲の FormatConditions コレクションを通して追加します。誰もがつまずくことが 2 つあります。Add の前に Delete しないとルールが 積み上がる こと、そして数式ルールでは参照が $D2（列を固定、行は自由）でなければ、間違ったセルをハイライトすることです。

Dim rng As Range: Set rng = ThisWorkbook.Worksheets("Sales").Range("A2:F1000")
rng.FormatConditions.Delete                              ' 先に古いルールを消す (冪等)
With rng.FormatConditions.Add(Type:=xlExpression, Formula1:="=$D2>1000")
    .Interior.Color = RGB(198, 239, 206)                 ' D 列が 1000 超なら行全体が緑になる
End With

VBA からセルを塗るには、まったく異なる 2 つのモードがあり、間違ったほうを選ぶことが静かなバグになります。Interior.Color をループで設定する — 1 回きりの塗装 — こともできますし、ルール を追加して、色付けを永遠に Excel に持たせることもできます。本記事は 2 つ目についてです。データが変わっても正しいままであり続けるほうであり、そして人が代わりに脆いループに手を伸ばしてしまうほうだからです。

この記事で学べること

考え方の軸 — ルールは生きた鏡、ループは写真
FormatConditions コレクションを通してルールを追加する
冪等に保つ習慣 — Add の前に Delete
実際に使う 2 つのルール型 — xlCellValue と xlExpression
参照の罠 — 行全体のハイライトに D2 や $D$2 ではなく $D2 が要る理由
静的なループが今も正しい道具である場面

考え方の軸：写真ではなく、生きた鏡

期限切れの行をループでハイライトすると、スナップショットができます。走った瞬間は正しく、誰かが期日を編集した瞬間に間違いになります — 色は動きません。ループを再実行するものが何もないからです。

' スナップショット: 今は正しく、次の編集で古びる。
Dim c As Range
For Each c In ws.Range("D2:D1000")
    If c.Value > 1000 Then c.EntireRow.Interior.Color = RGB(198, 239, 206)
Next c

条件付き書式のルールは種類が違います。条件を一度記述して Excel に手渡すと、Excel は再計算のたびに、永遠に再評価します。値を変えれば、色は同じ瞬間に追随します。それこそがコードに条件付き書式が存在する理由そのものです。ループは塗り、ルールは約束する。 色付けを「これを正しく保つ責任を持つのは誰か — 自分か、Excel か」と見たとたん、2 つのモードの選択はスタイルの問題ではなく、正しさの問題になります。

ルールを追加する：FormatConditions コレクション

あらゆる範囲は FormatConditions コレクションを備えています。そこに条件を Add すると、Add は新しい条件を返し、その .Interior、.Font、.Borders を続けて設定します。

Dim rng As Range: Set rng = ws.Range("B2:B1000")
With rng.FormatConditions.Add(Type:=xlCellValue, Operator:=xlLess, Formula1:="0")
    .Interior.Color = RGB(255, 199, 206)   ' 値がマイナスのとき赤い塗り
    .Font.Color = RGB(156, 0, 6)
End With

コレクションは ルールが適用される範囲 — ここでは B2:B1000 — に宿るので、ルールとその適用範囲は同じ息で設定されます。.Add はルールの Type を取り、（値ルールでは）Operator と 1 つか 2 つの Formula1/Formula2 のしきい値を取ります。その後はすべて、返された条件オブジェクトを書式設定するだけです。

最も大事な習慣：Add の前に Delete

あらゆる条件付き書式のマクロがいずれはまる失敗はこれです。.Add は 置き換え ません — 追記します。マクロを 2 回走らせれば範囲には同一のルールが 2 つ、シートをまたぐループで走らせれば数百になり、それぞれがわずかな性能コストで、手作業でほどくのは悪夢です。

rng.FormatConditions.Delete                 ' <- これを冪等に保つ 1 行
With rng.FormatConditions.Add(...)          ' これで毎回きっかり 1 つのルール
    ...
End With

「Add の前に Delete」を反射にしましょう。千回走らせても同じ結果になるマクロと、静かにゴミをためていくマクロの違いです。ユーザーが手で追加した既存のルールを残さなければならないなら、 FormatConditions を歩いて自分のものだけを取り除く、より外科的な削除をします — でも範囲の書式設定を所有するマクロなら、まず素直に Delete するのが正直な既定です。

実際に使う 2 つのルール型

Type の値はいくつかありますが、実務のほとんどを担うのは 2 つです。

xlCellValue — このセル自身 の値を比べます。Operator:=xlLess、xlGreater、xlBetween、 xlEqual。これは「セルを自分の数値で塗る」単純なケースです。
xlExpression — TRUE/FALSE を返す数式を評価します。これが強力なほうです。他の列を参照でき、1 つのフィールドに基づいて行全体を塗るのはこの方法です。

' 値ルール: セル自身の値がゼロ未満のとき赤く塗る。
rng.FormatConditions.Add Type:=xlCellValue, Operator:=xlLess, Formula1:="0"

' 数式ルール: D 列が 1000 を超えたら行全体を塗る。
ws.Range("A2:F1000").FormatConditions.Add Type:=xlExpression, Formula1:="=$D2>1000"

そのほかの AddDatabar、AddColorScale、AddIconSetCondition は、同じコレクションをより豊かなビジュアルで扱うものですが、値ルール対数式ルールを理解していれば、このモデルは理解できています。

参照の罠：行全体のハイライトに $D2 が要る理由

これは「ルールが間違ったセルをハイライトする」第 1 位のバグで、純粋にスプレッドシートの仕組みです。xlExpression の数式は 適用範囲の左上のセルを基準に 評価され、そこから Excel は数式をドラッグするのと同じ要領で、すべてのセルへ歩かせます。だから、何が動くかはドル記号が決めます。

=$D2>1000 — 列を固定、行は自由。ある行のどのセルもその行の D 列をテストするので、行全体が一緒に点灯します。行のハイライトで欲しいのはこれです。
=D2>1000 — 何も固定しない。参照は列方向にもずれるので、2 行目は D2 をテストしても、2 行目の B 列は E2 をテストし、すべてが滲みます。
=$D$2>1000 — すべて固定。範囲全体のどのセルも単一のセル D2 をテストします — だからブロック全体が一斉にオンかオフになります。

アンカーを間違えると、ルールは（エラーなく)「動き」ますが、でたらめに色を付けます。直し方は、ドラッグする数式とまったく同じように考えることです。テストする列を固定し、行は相対のままにします。

静的なループが今も正しい場面

ルールが常に答えとは限りません。色がデータを 追わない べきときは、素朴な Interior.Color ループを使いましょう — これから凍結して PDF に書き出す 1 回きりのレポートで、誰かがセルを編集した後もハイライトを焼き付けて変えたくない場合です。その場合、生きたルールは間違った道具です。固定されたスナップショットであるべき文書を、再び色付けし続けてしまうからです。

判断はきれいな一線です。シートが編集されても色が正しいままであるべきならルールを、色を今この瞬間のまま凍結すべきならループを使う。 たいていはルールが欲しいはずで — だからこそ、反射でループに手を伸ばすことが、学び直す価値のある間違いなのです。

ExcelMaster の活用

コードでの条件付き書式には、静かな罠が 3 つあります。先に何も削除しないせいで積み上がるルール、 $D2 の代わりに $D$2 や D2 でアンカーされて間違ったセルを塗る数式ルール、そしてルールがふさわしい場所で使われて次の編集でハイライトが古びる脆いループ。どれもエラーなく走り、あとで間違って見えます。

ExcelMaster なら、結果を説明するだけで済みます。「合計が 1000 を超える行をハイライトして」「マイナスを赤くして」「期限切れの項目にフラグを立てて、それを生かしておいて」。マクロが冪等になるよう Add の前に FormatConditions.Delete を書き、正しい行全体のハイライトのために数式ルールを $D2 でアンカーし、本当に凍結したレポートが欲しいときだけ静的なループに落とします。ブックもコードもあなたの手元に残り — 色は、あなたが意図したとおりにデータを追います。

よくある質問

VBA で条件付き書式を追加するには？

範囲の FormatConditions コレクションにルールを追加します。 Range("B2:B1000").FormatConditions.Add Type:=xlCellValue, Operator:=xlLess, Formula1:="0" として、返された条件の書式（.Interior.Color、.Font.Color）を設定します。ルールは適用先の範囲に宿り、データが変わるたびに Excel が自動で再評価します。

条件付き書式のルールが増え続けるのはなぜ？

FormatConditions.Add が置き換えではなく追記するので、走るたびにコピーがもう 1 つ増えるからです。 Add の前に Range(...).FormatConditions.Delete を呼んでマクロを冪等に保ちましょう — 何回走らせてもきっかり 1 つのルールです。シート全体をクリアするには Cells.FormatConditions.Delete を使います。

VBA の条件付き書式で行全体をハイライトするには？

行全体の範囲に数式ルールを使い、行ではなくテストする列を固定します。 Range("A2:F1000").FormatConditions.Add Type:=xlExpression, Formula1:="=$D2>1000"。$D2 の参照（列を固定、行は相対）により、各行が自分の D 列をテストするので、行全体が一緒に色づきます。 $D$2 や D2 は間違ったセルをハイライトします。

xlCellValue と xlExpression の違いは？

xlCellValue は Operator（xlLess、xlGreater、xlBetween）でしきい値と各セルを比べます — セルを自分の値で塗るのに向きます。xlExpression は TRUE/FALSE を返す数式を評価し、他の列を参照できるので、1 つのフィールドから行全体を塗るのはこの方法です。列をまたぐものは何であれ数式ルールを使いましょう。

セルを塗るのに条件付き書式と VBA ループのどちらを使うべき？

シートが編集されても色が正しいままでなければならないときはルールを使いましょう — Excel は変更のたびに FormatConditions のルールを再評価します。For Each ... Interior.Color のループは、凍結して書き出すつもりの静的な 1 回きりのレポートのときだけ使い、ハイライトを焼き付けて、あとの編集後に再適用させたくない場合に使います。

検証環境

動作確認: Excel 365 (Windows 11), VBA 7.1 — 最終確認 2026-09-08。

関連ガイド: VBA Cell Color · VBA ColorIndex · VBA RGB · VBA Font · VBA For Each