Wauvel

Excel like a finance pro.

← All VBA snippets

Add conditional formatting from a macro

One rule instead of forty fragments — the reason an old workbook gets slow.

1What it does

Conditional formatting applied by hand fragments over time. Every copy, paste and row insert splits a rule into more rules covering smaller ranges, until a sheet that had three rules has four hundred and takes seconds to scroll. Applying the rules from a macro puts them back to exactly what they should be: clear the old ones, add each rule once across its full range. It's also how every monthly report gets the same formatting without anyone remembering which thresholds were used last time.

2The code

Sub ApplyVarianceFormatting()
    Dim ws As Worksheet, rng As Range, lastRow As Long
    Dim fc As FormatCondition

    Set ws = ActiveSheet
    lastRow = ws.Cells(ws.Rows.Count, 1).End(xlUp).Row
    If lastRow < 2 Then Exit Sub
    Set rng = ws.Range("A2:E" & lastRow)

    ' Start clean — this is what undoes the fragmentation.
    rng.FormatConditions.Delete

    ' Whole ROW red when the variance in column E is worse than -10%.
    ' Column locked with $, row relative, written for the FIRST row.
    Set fc = rng.FormatConditions.Add(Type:=xlExpression, Formula1:="=$E2<-0.1")
    fc.Interior.Color = RGB(255, 226, 220)
    fc.Font.Color = RGB(180, 40, 20)
    fc.StopIfTrue = True

    ' Then a quieter tint for anything worse than -5%.
    Set fc = rng.FormatConditions.Add(Type:=xlExpression, Formula1:="=$E2<-0.05")
    fc.Interior.Color = RGB(253, 238, 205)

    ' And a colour scale on the variance column alone.
    ws.Range("E2:E" & lastRow).FormatConditions.AddColorScale ColorScaleType:=3
End Sub

Paste into the Visual Basic Editor (Alt + F11 → Insert → Module), then run and save as macro-enabled (.xlsm).

New to macros? Set up in 5 minutes▾
  1. 1

    Don't see the Developer tab in the ribbon?

    You don't strictly need it — Alt + F11 opens the editor directly — but it makes running macros easier.
    • Windows: File → Options → Customize Ribbon → tick Developer in the right-hand list → OK.
    • Mac: Excel → Preferences → Ribbon & Toolbar → tick Developer → Save.
  2. 2

    Paste in the code

    Press Alt + F11 to open the Visual Basic editor, then Insert → Module and paste the snippet's code into the blank window. Close it with Alt + Q.
  3. 3

    Run it

    Press Alt + F8, pick the macro's name, and click Run — that's it. (Pasted a custom function instead? Just type it into a cell like any built-in: =GrossMargin(B2, B3).)
  4. 4

    Keep the macro — save as .xlsm

    File → Save As → Excel Macro-Enabled Workbook (.xlsm). A plain .xlsx silently drops the code when you save.
  5. 5

    Macros blocked?

    Click Enable Content on the yellow bar. If you downloaded the file, you may first need to right-click it → Properties → tick Unblock → OK, then reopen.

Heads up: macros can't be undone with Ctrl + Z — save a copy before running one that changes your workbook.

3When you use it

  • Re-apply a report's variance highlighting cleanly every month.
  • Fix a slow inherited workbook by replacing hundreds of fragmented rules.
  • Stripe whole rows by status without anyone editing rules by hand.

4See it in action

A simulation — press Run to perform what the macro does to a sample workbook.

Home → Conditional Formatting → Manage Rules
Times run0
Rules on the sheet0

Run it a few times, as a monthly report would.

5Pitfalls

Rules pile up every run, because the macro adds without clearing.

Fix: FormatConditions.Delete on the range first, as above.

Only the first column colours, or the wrong rows do.

Fix: The formula is relative to the FIRST cell of the range. Lock the column with $ and leave the row unlocked: =$E2.

A lower rule never shows because a higher one already matched.

Fix: Order matters, and StopIfTrue on the stronger rule makes the priority explicit.

Formula1 in a non-English Excel fails with a formula error.

Fix: FormatConditions.Add uses the local-language formula syntax. Keep expressions to simple comparisons, or build them with the local function names.

6No-code alternatives

  • Conditional Formatting by hand — Fine for a one-off. The macro is for reports rebuilt monthly and for cleaning fragmented rules. See the tip.
  • Manage Rules → delete the duplicates — Home → Conditional Formatting → Manage Rules shows the fragmentation; the macro just fixes it faster.

Rather not write macros? Wauvel's free tools generate branded, formula-driven Excel for you — no VBA required.

Learn the moves here — or let Wauvel run them on your numbers.

Meet your AI CFO →

One CFO-grade Excel tip a week

A short, practical email for finance operators — functions, shortcuts, and the moves that save an afternoon. Free, unsubscribe anytime.