🚀The world's best VBA AI has evolved. ExcelMaster is now an autonomous Agent.Read more →
Back to Blog

VBA Conditional Formatting in Excel — Rules That Stay Live, Not a Loop That Goes Stale

|

VBA Conditional Formatting in Excel — Rules That Stay Live, Not a Loop That Goes Stale

TL;DR — A conditional-format rule is a standing instruction you give Excel once; Excel re-runs it on every edit. A For Each ... Interior.Color loop is a photograph — correct the instant it runs, stale the instant a value changes. You add rules through a range's FormatConditions collection. Two things trip everyone up: rules pile up if you do not Delete before you Add, and in an expression rule the reference must be $D2 (column locked, row free) or it highlights the wrong cells.

Dim rng As Range: Set rng = ThisWorkbook.Worksheets("Sales").Range("A2:F1000")
rng.FormatConditions.Delete                              ' clear old rules first (idempotent)
With rng.FormatConditions.Add(Type:=xlExpression, Formula1:="=$D2>1000")
    .Interior.Color = RGB(198, 239, 206)                 ' whole row goes green when column D > 1000
End With

Coloring a cell from VBA has two completely different modes, and picking the wrong one is a quiet bug. You can set Interior.Color in a loop — a one-time paint job — or you can add a rule and let Excel own the coloring forever. This guide is about the second, because it is the one that stays correct when the data changes, and the one people reach for a brittle loop instead of.

What you'll learn

  • The mental model — a rule is a live mirror, a loop is a photograph
  • Adding a rule through the FormatConditions collection
  • The habit that keeps it idempotent — Delete before you Add
  • The two rule types you actually use — xlCellValue and xlExpression
  • The reference trap — why whole-row highlighting needs $D2, not D2 or $D$2
  • When a static loop is still the right tool

The mental model: a live mirror, not a photograph

Highlighting overdue rows with a loop produces a snapshot. It is correct the moment it runs and wrong the moment someone edits a due date — the color does not move, because nothing re-runs the loop:

' Snapshot: correct now, stale after the next edit.
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

A conditional-format rule is different in kind. You describe the condition once, hand it to Excel, and Excel re-evaluates it on every recalculation, forever. Change a value and the color follows the same instant. That is the whole reason conditional formatting exists in code: a loop paints, a rule promises. Once you see coloring as "who owns keeping this correct — me, or Excel," the choice between the two modes stops being a style question and becomes a correctness one.

Adding a rule: the FormatConditions collection

Every range carries a FormatConditions collection. You Add a condition to it; Add returns the new condition, whose .Interior, .Font and .Borders you then set:

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)   ' red fill when the value is negative
    .Font.Color = RGB(156, 0, 6)
End With

The collection lives on the range the rule applies to — B2:B1000 here — so the rule and its scope are set in the same breath. .Add takes the rule Type, and (for a value rule) an Operator and one or two Formula1/Formula2 thresholds. Everything after that is just formatting the condition object it handed back.

The habit that matters most: Delete before Add

Here is the failure mode that catches every conditional-formatting macro eventually. .Add does not replace — it appends. Run the macro twice and the range has two identical rules; run it in a loop over sheets and you get hundreds, each a tiny performance cost and a nightmare to untangle by hand:

rng.FormatConditions.Delete                 ' <- the one line that keeps this idempotent
With rng.FormatConditions.Add(...)          ' now there is exactly one rule, every run
    ...
End With

Make Delete-before-Add reflexive. It is the difference between a macro you can run a thousand times with the same result and one that silently accumulates cruft. If you must keep pre-existing rules the user added by hand, delete more surgically by walking FormatConditions and removing only yours — but for a macro that owns the range's formatting, a clean Delete first is the honest default.

The two rule types you actually use

There are several Type values, but two carry almost all real work:

  • xlCellValue — compare this cell's value. Operator:=xlLess, xlGreater, xlBetween, xlEqual. This is the simple "color the cell by its own number" case.
  • xlExpression — evaluate a formula that returns TRUE/FALSE. This is the powerful one: it can reference other columns, which is how you color an entire row based on one field.
' Value rule: color a cell red when its own value is below zero.
rng.FormatConditions.Add Type:=xlCellValue, Operator:=xlLess, Formula1:="0"

' Expression rule: color the WHOLE row when column D exceeds 1000.
ws.Range("A2:F1000").FormatConditions.Add Type:=xlExpression, Formula1:="=$D2>1000"

The extras — AddDatabar, AddColorScale, AddIconSetCondition — are the same collection with richer visuals, but if you understand value-versus-expression you understand the model.

The reference trap: why whole-row highlighting needs $D2

This is the number-one "my rule highlights the wrong cells" bug, and it is pure spreadsheet mechanics. An xlExpression formula is evaluated relative to the top-left cell of the applied range, then Excel walks it across every cell the way it would drag a formula. So the dollar signs decide what moves:

  • =$D2>1000 — column locked, row free. Every cell in a given row tests that row's column D, so the whole row lights up together. This is what you want for row highlighting.
  • =D2>1000 — nothing locked. The reference drifts by column too, so row 2 tests D2, but column B of row 2 tests E2, and it all smears.
  • =$D$2>1000 — everything locked. Every cell in the whole range tests the single cell D2 — so the entire block is on or off together.

Get the anchoring wrong and the rule "works" (no error) but colors nonsense. The fix is to think about it exactly as you would a dragged formula: lock the column you are testing, leave the row relative.

When a static loop is still right

Rules are not always the answer. Use a plain Interior.Color loop when the color should not track the data — a one-off report you are about to freeze and export as a PDF, where you want the highlight baked in and unchanging even after someone edits a cell. In that case a live rule is the wrong tool: it would keep re-coloring a document that is supposed to be a fixed snapshot.

The judgment is a clean line: if the color should stay correct as the sheet is edited, use a rule; if the color should freeze as it is right now, use a loop. Most of the time you want the rule — which is exactly why reaching for the loop by reflex is the mistake worth unlearning.

How ExcelMaster helps

Conditional formatting in code has three quiet traps: rules that pile up because nothing deletes them first, an expression rule anchored $D$2 or D2 instead of $D2 so it colors the wrong cells, and a brittle loop used where a rule belonged so the highlight goes stale on the next edit. Each one runs without error and looks wrong later.

ExcelMaster lets you describe the outcome — "highlight rows where the total is over 1000," "shade negatives red," "flag overdue items and keep it live." It writes a FormatConditions.Delete before it Adds so the macro is idempotent, anchors expression rules as $D2 for correct whole-row highlighting, and only drops to a static loop when you actually want a frozen report. You keep the workbook and the code — and the color tracks the data the way you meant.

Frequently asked questions

How do I add conditional formatting in VBA?

Add a rule to a range's FormatConditions collection: Range("B2:B1000").FormatConditions.Add Type:=xlCellValue, Operator:=xlLess, Formula1:="0", then set the returned condition's format (.Interior.Color, .Font.Color). The rule lives on the range it applies to, and Excel re-evaluates it automatically whenever the data changes.

Why do my conditional formatting rules keep multiplying?

Because FormatConditions.Add appends rather than replaces, so every run adds another copy. Call Range(...).FormatConditions.Delete before you Add to keep the macro idempotent — exactly one rule, no matter how many times it runs. To clear the whole sheet, use Cells.FormatConditions.Delete.

How do I highlight an entire row with conditional formatting in VBA?

Use an expression rule on the whole row range and lock the tested column, not the row: Range("A2:F1000").FormatConditions.Add Type:=xlExpression, Formula1:="=$D2>1000". The $D2 reference (column locked, row relative) makes each row test its own column D, so the full row colors together. $D$2 or D2 will highlight the wrong cells.

What is the difference between xlCellValue and xlExpression?

xlCellValue compares each cell against a threshold with an Operator (xlLess, xlGreater, xlBetween) — good for coloring a cell by its own value. xlExpression evaluates a formula returning TRUE/FALSE and can reference other columns, which is how you color a whole row from one field. Use expression rules for anything cross-column.

Should I use conditional formatting or a VBA loop to color cells?

Use a rule when the color must stay correct as the sheet is edited — Excel re-evaluates a FormatConditions rule on every change. Use a For Each ... Interior.Color loop only for a static one-off report you intend to freeze and export, where you want the highlight baked in and not re-applied after later edits.

Tested in

Tested in: Excel 365 (Windows 11), VBA 7.1 — last verified 2026-09-08.

Related guides: VBA Cell Color · VBA ColorIndex · VBA RGB · VBA Font · VBA For Each