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, orSEARCH. - 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
ANDorOR. - 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
- Excel Power Query Weighted Average: How to Calculate
- Excel Power Query Undo: What Works and What Doesn’t
- Excel Power Query Ungroup: How to Split Grouped Rows
Try it: the Column Letter to Number: aA is 27, XFD is 16,384. Converts both ways.
More in VBA & macros
- Tiny Macro Recorder: What TinyTask Can and Can’t DoIf you mean TinyTask, it is a small Windows macro recorder that captures mouse and keyboard actions and…
- Excel VBA: Clear Contents of Multiple RangesTo clear the contents of multiple ranges in Excel VBA, use the Range.ClearContents method on each range, or…
- Free Macro Recorder for Windows 11: Best OptionsYes. Windows 11 users can record and replay desktop actions for free with Microsoft Power Automate for desktop,…
- Excel VBA Macro Best Practices for Reliable WorkbooksFor reliable Excel VBA macros, make each procedure do one clear job, declare variables with Option Explicit, qualify…
- VBA Sort Key: How Key1 and SortFields WorkIn VBA, a sort key is the cell or range that tells Excel which column’s values to use…
- Excel VBA Copy Paste Transpose: Working Code and TipsTo copy a range and paste it transposed with Excel VBA, copy the source range and use PasteSpecial…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.