Conditional Formatting VBA: Rules, Examples, and Tips

In Excel for Microsoft 365 on Windows, VBA creates conditional formatting by adding a rule to a range’s FormatConditions collection. For example, a formula rule can color a task row when its due date in column D has passed; the color then updates as the worksheet values change.

What conditional formatting VBA means

Conditional formatting is an Excel feature that changes a cell’s appearance when a rule is true. VBA is Excel’s programming language, so conditional formatting VBA means using a macro to create, change, prioritize, or remove those rules. It does not usually mean that VBA paints a cell once and leaves the color there: a conditional formatting rule remains attached to the range and Excel evaluates it as values change.

This distinction matters. A macro that sets Range.Interior.Color applies a regular fill, which stays until another action changes it. A macro that adds a conditional formatting rule defines a condition and a format that Excel can recalculate. If the condition becomes false, the conditional format no longer applies, although any underlying regular cell formatting remains.

VBA is useful when a workbook needs the same rules applied repeatedly, when rules depend on a changing data range, or when a report is generated automatically. For a one-time rule on a small range, Excel’s Conditional Formatting ribbon is often quicker and easier to maintain.

How VBA conditional formatting works

Each worksheet range has a FormatConditions collection. The Add method creates a rule, and the returned rule object can be configured with a fill, font, border, priority, or other supported formatting. The rule’s type determines how Excel interprets its condition.

xlCellValue compares a cell with a value or formula result, such as a number below zero.
xlExpression evaluates a worksheet formula. This is the flexible choice for rules that refer to other cells or format an entire row.
Other rule types include color scales, data bars, icon sets, text strings, blanks, duplicate or unique values, and top or bottom values.

For formula rules, the formula is written as if it applies to the top-left cell of the selected range. Excel adjusts relative references across the rest of the range. Thus, a range beginning at B2 can use a reference to D2 to make a decision for each row. A dollar sign fixes a column or row: $D2 always checks column D but lets the row number adjust.

In VBA, Formula1 takes the rule expression as a string. For the Formula1 property, use Excel’s formula syntax, including English function names and comma separators, even if the Excel interface is localized. A formula rule should begin with an equals sign.

Create a formula rule for overdue tasks

Suppose a worksheet named Tasks has task details in B2:F100 and due dates in D2:D100. To shade an entire task row when its date is earlier than today, the macro can apply an expression rule to B2:F100. The formula checks that D contains a date and that the date is overdue.

Before running a macro, save a copy of the workbook. VBA macros cannot be undone with Ctrl+Z, and running code against the wrong range can alter a large part of a worksheet.

Option Explicit

Sub AddOverdueTaskFormatting()
    Dim ws As Worksheet
    Dim target As Range
    Dim rule As FormatCondition

    Set ws = ThisWorkbook.Worksheets("Tasks")
    Set target = ws.Range("B2:F100")

    ' Check the date in column D for each row.
    Set rule = target.FormatConditions.Add( _
        Type:=xlExpression, _
        Formula1:="=AND(ISNUMBER($D2),$D2<TODAY())")

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

The references are intentional: the dollar sign locks the test to column D, while the row number remains relative. Because the applied range begins on row 2, the formula’s D2 reference is evaluated for row 2, then adjusted for each subsequent row. ISNUMBER prevents blank cells from being treated as overdue dates. The rule does not check whether a task is complete; if completion status is in another column, add that condition to the formula and lock its column appropriately.

Build a simpler value-based rule

For a direct comparison, use xlCellValue. The following example marks negative numbers in E2:E200 with red text. It assumes a worksheet called Sales and applies only to the specified cells.

Option Explicit

Sub FormatNegativeValues()
    Dim ws As Worksheet
    Dim target As Range
    Dim rule As FormatCondition

    Set ws = ThisWorkbook.Worksheets("Sales")
    Set target = ws.Range("E2:E200")

    ' Highlight values below zero.
    Set rule = target.FormatConditions.Add( _
        Type:=xlCellValue, _
        Operator:=xlLess, _
        Formula1:="0")

    rule.Font.Color = RGB(192, 0, 0)
End Sub

Here, Formula1 is the comparison value, not a worksheet formula beginning with an equals sign. The rule compares each cell in the target range with zero. If the range contains text, those entries are not negative numeric values and are not highlighted by this comparison.

Choose the right range and rule type

The range is as important as the condition. If only the due-date cells should change appearance, apply the rule to D2:D100. If every cell in a task row should be shaded, apply it to B2:F100 and write the formula using the top-left row of that range. Selecting a broader range than necessary can slow complex workbooks and make rules harder to understand.

Use a cell-value rule for simple numeric comparisons. Use an expression rule when the condition involves another column, multiple tests, or row-level logic. For instance, a report might need to flag a record only when its status says Pending and its date has passed. A formula rule can combine both conditions. Keep formulas readable, and test one or two rows before expanding the rule to thousands of records.

Conditional formatting is designed for visual signals, not for changing the underlying values. A red font does not turn a number negative, and a green fill does not certify that data is correct. If another formula, macro, or export needs the condition as data, calculate or store that status separately rather than trying to read the displayed color as the business logic.

Edit, remove, and prioritize rules

Adding a rule does not replace existing rules. Each call to FormatConditions.Add adds another rule, so running the same macro repeatedly can create duplicates. Before automating a rule, decide whether the macro should add a new rule, update a known rule, or intentionally clear existing rules. The FormatConditions.Delete method removes all conditional formatting rules from the range it is called on, so use it only when clearing every rule in that range is intended.

When rules overlap, Excel evaluates them according to their priority. A rule’s SetFirstPriority method can move it to the top of the priority list, and the StopIfTrue property can prevent lower-priority rules from being evaluated when the current rule is true. These settings are useful when formats conflict, but they should be used deliberately: a higher-priority rule may hide a format that users expect to see.

To inspect or adjust rules without changing the macro, select the relevant cells and choose Home > Conditional Formatting > Manage Rules. In the Rules Manager, check the “Show formatting rules for” selection. It can be set to the current selection, the current worksheet, or another scope; a rule may appear to be missing simply because the manager is showing a different scope.

Common problems and how to troubleshoot them

The wrong row is highlighted: Check the top-left cell of the applied range against the formula’s relative row reference. A formula designed for a range beginning on row 2 may be offset if applied starting on row 1.
The wrong column is checked: Use a dollar sign before the column letter, as in $D2, when every formatted cell in a row should test column D. Use a fully relative reference only when the test should shift across columns too.
The rule works in one sheet but not another: Confirm the worksheet name, range, and data layout. A macro that expects a date in column D cannot correctly evaluate a sheet where dates are in column E.
A formula is rejected or behaves differently: Verify the rule type and formula syntax. For VBA’s Formula1, use the formula form expected by Excel rather than copying a localized function name from a translated interface.
Nothing changes after running the macro: Confirm that macros are allowed for the workbook, that the macro ran without an error, and that the condition is actually true for the data. Also check whether another rule with higher priority affects the visible format.
The rule appears more than once: The procedure may have been run repeatedly. Inspect the Rules Manager and remove duplicates or revise the macro so it updates the intended rule instead of adding another one.

For a formula rule, test the logic in a worksheet cell or against a small sample first. Also check whether dates are real Excel date values rather than text that merely looks like a date; formatting cannot make text behave as a numeric date.

VBA versus the ribbon, platform support, and safety

The ribbon is a good choice for a rule that users need to inspect and change manually. VBA is preferable when setup must be repeatable, conditional formatting is part of a larger report-generation process, or the target range changes based on workbook logic. A hybrid approach is common: use VBA to establish a consistent rule, then document the rule so workbook users can maintain it.

In Excel for Microsoft 365 on Windows, open the Visual Basic Editor with Alt+F11 and open the Macro dialog with Alt+F8. On Mac, the corresponding shortcuts are commonly Option+F11 and Option+F8; some keyboards require holding Fn as well. You can also access macro tools through Excel’s menus or ribbon. Excel for the web does not run VBA macros, although it can work with many workbook features and may display conditional formatting created in the desktop app. Use desktop Excel to run or edit VBA.

Macros can make broad workbook changes, so only enable code from a source you trust. Save a separate copy before running code, especially if it deletes or rewrites existing rules. Macro actions cannot be reversed with Ctrl+Z. If a workbook is shared, explain which ranges the macro changes and whether users should rerun it after adding rows.

Frequently Asked Questions

Can VBA apply conditional formatting to an entire row?

Yes. Apply the rule to the columns in each row that should receive the format, then use an expression rule that tests the relevant row’s value. Lock the test column with a dollar sign, but leave the row relative so the condition adjusts for each row.

Does VBA conditional formatting update when cell values change?

Yes. A conditional formatting rule remains on its target range and Excel recalculates whether the condition is true as worksheet values change. This differs from a macro that applies a fixed fill color, which does not automatically respond when the data changes.

How do I avoid adding duplicate rules every time I run a macro?

Design the macro to find and update the specific rule it owns, or intentionally remove rules from a carefully limited range before rebuilding them. Avoid calling FormatConditions.Delete on a range that contains rules users need to keep, because that method clears all conditional formatting rules there.

Can conditional formatting VBA use a formula that refers to another column?

Yes. Use an expression rule and make the reference relative or absolute as needed. For example, to format several cells in each row based on a value in column D, lock column D and leave the row number relative. Check that the formula is aligned with the first row of the applied range.

Can I run conditional formatting VBA in Excel for the web?

No. Excel for the web does not execute VBA macros. Open the workbook in the Excel desktop application to run or edit the macro; the web version may still display conditional formatting rules already stored in the workbook.

Try it: the Column Letter to Number: aA is 27, XFD is 16,384. Converts both ways.

More in VBA & macros

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.