Excel VBA Conditional Formatting Formula Guide

VBA & macros · Updated November 22, 2026

The core formula for Excel VBA conditional formatting is Range.FormatConditions.Add combined with a formula string, for example Range("A1:A10").FormatConditions.Add Type:=xlExpression, Formula1:="=A1>100". This applies a rule that highlights cells where the formula returns TRUE, and you can modify or delete rules using FormatConditions collection methods.

What Conditional Formatting Does in VBA

Conditional formatting in Excel changes a cell’s appearance (fill, font, border) based on a condition. In the user interface, you create these rules via Home > Conditional Formatting. In VBA, you automate the same process using the FormatConditions collection of a Range object. Each rule is a FormatCondition object. You can add, modify, or remove rules programmatically, which is useful when you need to apply the same formatting across many sheets or workbooks, or when the rule must change dynamically based on cell values.

How to Add a Formula-Based Rule in VBA

The most common method is FormatConditions.Add. The syntax is:

Range.FormatConditions.Add(Type:=xlExpression, Formula1:="formula string")

Here’s a complete example that highlights cells in A1:A10 if they are greater than 100:

Sub HighlightLargeValues()
    Dim rng As Range
    Set rng = ThisWorkbook.Sheets("Sheet1").Range("A1:A10")
    ' Clear any existing rules
    rng.FormatConditions.Delete
    ' Add a formula-based rule
    rng.FormatConditions.Add Type:=xlExpression, Formula1:="=A1>100"
    ' Set the formatting (e.g., yellow fill)
    rng.FormatConditions(1).Interior.Color = RGB(255, 255, 0)
End Sub

Remember: the formula must be relative to the upper-left cell of the range you apply it to. In the example, because the range starts at A1, the formula uses A1. If the range were B2:B10, the formula would reference B2.

Choosing Between Formula and Cell Value Rules

VBA offers several Type options: xlCellValue (comparison against a value), xlExpression (formula), xlColorScale, xlDataBar, and xlIconSet. Use xlExpression when:

  • The condition spans multiple columns or rows (e.g., highlight entire row if column C is “Yes”).
  • You need to compare against another cell or use functions like AND, OR, ISBLANK, or SEARCH.
  • The rule depends on a value outside the selected range.

For simple comparisons like “greater than 100”, xlCellValue is simpler:

rng.FormatConditions.Add Type:=xlCellValue, Operator:=xlGreater, Formula1:="100"

But formula rules are more flexible. For example, to highlight entire rows where the value in column C is “Overdue”:

Dim rng As Range
Set rng = ThisWorkbook.Sheets("Sheet1").Range("A2:D100")
rng.FormatConditions.Add Type:=xlExpression, Formula1:="=$C2=""Overdue"""
' Note the doubled quotes inside the string

Here, the formula uses a mixed reference: column C is absolute ($C), row is relative (2). This ensures each row checks its own C column.

Modifying and Deleting Existing Rules

To change a rule, access it by index or name (if you set a name). For example:

' Change the first rule's formula
rng.FormatConditions(1).Formula1 = "=A1>200"

To delete a specific rule, use Delete:

rng.FormatConditions(1).Delete

To delete all rules on a range, use rng.FormatConditions.Delete. Be careful: deleting rules is not undoable with Ctrl+Z, so always save a copy of the workbook before running a macro that modifies formatting.

Common Problems and How to Fix Them

  • Formula not working as expected: Ensure the formula uses relative references correctly. If you apply a rule to A1:A10, the formula should reference A1, not A2. Also, check that the formula returns TRUE/FALSE, not a number.
  • Quotes inside formulas: When a formula contains text strings, you must double the quotes in VBA. For example, "=A1=""Yes""".
  • Rule order matters: Excel evaluates rules in order and stops at the first TRUE rule if “Stop If True” is enabled. In VBA, you can set FormatConditions(i).StopIfTrue = True.
  • Performance issues: Applying many rules to large ranges can slow down Excel. Use as few rules as possible, and consider using a formula that covers multiple conditions with AND or OR.
  • Mac vs Windows: VBA syntax is identical, but keyboard shortcuts differ. For example, to open the VBA editor, use Alt+F11 on Windows and Opt+F11 on Mac.

Comparing VBA Conditional Formatting to Other Methods

You can achieve the same result without VBA using the built-in conditional formatting UI, but VBA offers advantages:

  • Automation: Apply the same rule to multiple sheets or workbooks in one run.
  • Dynamic rules: Change rules based on user input or cell values.
  • Complex logic: Use VBA to compute conditions that are hard to express in a single formula.

However, VBA rules are not as easy to maintain for non-programmers. Also, if you share the workbook, users must enable macros. For simple static rules, the UI is simpler and safer.

Safety and Best Practices

Before running any VBA macro that modifies formatting, always make a backup copy of your workbook. Macros cannot be undone with Ctrl+Z. Additionally, test your macro on a small range first. Use On Error Resume Next or error handling to avoid runtime errors if a range doesn’t exist. Finally, avoid using Select or Activate; instead, reference ranges directly for faster and more reliable code.

Frequently Asked Questions

How do I apply conditional formatting to an entire row based on a cell value?

Use a formula rule with a mixed reference. For example, to highlight rows in A2:D100 where column C equals “Yes”, apply the rule to A2:D100 with the formula =$C2="Yes". The $C makes the column absolute, and the row relative, so each row checks its own C value.

Can I use a VBA function inside the conditional formatting formula?

Yes, you can use any worksheet function that works in a normal formula, such as AND, OR, ISBLANK, SEARCH, etc. You cannot use VBA user-defined functions (UDFs) directly in conditional formatting formulas unless you use a workaround like a helper column.

How do I remove all conditional formatting from a worksheet using VBA?

Use Cells.FormatConditions.Delete. For example: ThisWorkbook.Sheets("Sheet1").Cells.FormatConditions.Delete. This removes all rules from every cell on the sheet.

Why does my conditional formatting formula work in the UI but not in VBA?

Often it’s due to quote escaping. In VBA, a string containing a formula with text must have double quotes doubled. For example, Formula1:="=A1=""Yes""". Also, ensure you are using the correct relative reference for the top-left cell of the range.

Can I set the priority of conditional formatting rules in VBA?

Yes, the order of rules in the FormatConditions collection determines priority. Rules are evaluated in order. You can reorder by moving rules, but it’s easier to delete and re-add them in the desired order. Also, set StopIfTrue to control whether the first TRUE rule stops evaluation.

Is there a way to apply conditional formatting to non-contiguous ranges?

Yes, you can union ranges with Union and then apply the rule to the union. For example: Set rng = Union(Range("A1:A10"), Range("C1:C10")) then add the rule to rng. The formula must reference the top-left cell of each area appropriately, so use a formula that works for both areas, or apply the rule separately to each area.

Related

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.