Fix Excel VBA Icon Set Conditional Formatting with Left-Cell Values
Question details
Apply a three-arrow Excel icon set through VBA that compares each target cell with the value immediately to its left.

- Product
- Microsoft Excel VBA conditional formatting
- Device & OS
- not provided
- Scenario
- The user loops through a worksheet range and dynamically adds green, yellow, and red arrow icon formatting to each cell.
- Observed behavior
- The icon set criteria need to be configured in the correct order so each cell can use its neighboring left-cell value as the comparison threshold.
Confirm that the target range starts at least one column to the right of the comparison values, because the macro uses Offset(0, -1). Remove or account for existing conditional formatting if the cells contain rules that should be retained.
Set three-arrow icon criteria 2 and 3 in reverse order
Use this approach when adding an Excel xl3Arrows icon set with VBA and comparing every cell to the cell directly on its left.
For a three-arrow icon set, Excel creates IconCriteria(1) automatically. Configure only IconCriteria(2) and IconCriteria(3), setting them in reverse order and assigning each criterion the adjacent left-cell value.
In Excel, press Alt+F11, then select Insert > Module to create or open a standard VBA module.
Paste the following macro into the module: Sub UpdateIconSets() Dim row As Integer, col As Integer For row = 3 To 12 For col = 3 To 12 With Cells(row, col) .FormatConditions.Delete With .FormatConditions.AddIconSetCondition .IconSet = ActiveWorkbook.IconSets(xl3Arrows) .ReverseOrder = False .ShowIconOnly = False With .IconCriteria(2) .Type = xlConditionValueNumber .Operator = xlGreaterEqual .Value = Cells(row, col).Offset(0, -1) End With With .IconCriteria(3) .Type = xlConditionValueNumber .Operator = xlGreater .Value = Cells(row, col).Offset(0, -1) End With End With End With Next col Next row End Sub
Change the For row = 3 To 12 and For col = 3 To 12 lines to match your target range. Keep the first target column at least one column to the right of the source comparison values.
Place the cursor inside UpdateIconSets and press F5, or return to Excel and choose Developer > Macros > UpdateIconSets > Run.

Clear existing conditional formatting before adding the icon set
Use this option when previous formatting rules conflict with, duplicate, or override the new VBA icon-set rule.
Use WPS Spreadsheet for familiar worksheet conditional formatting
This VBA icon-set behavior is part of Microsoft Excel's conditional-formatting object model. If you need a free, lightweight office suite for worksheet editing and familiar spreadsheet formatting tools, WPS Office provides a smooth alternative with a familiar interface and easy migration from common Microsoft Office files.

Frequently Asked Questions
Why do I configure only IconCriteria(2) and IconCriteria(3) for xl3Arrows?
An Excel three-arrow icon set creates IconCriteria(1) automatically. In VBA, configure the remaining two criteria through .IconCriteria(2) and .IconCriteria(3).
How can a VBA icon set compare a cell with the cell to its left?
Set the criterion value to Cells(row, col).Offset(0, -1) while looping through the target cells. Offset(0, -1) refers to the cell in the same row, one column to the left.
Which operators should I use for the three-arrow icon-set criteria?
For this left-cell comparison setup, use xlGreaterEqual for IconCriteria(2) and xlGreater for IconCriteria(3), following the reverse criterion configuration required by the icon-set rule.
Will FormatConditions.Delete remove other formatting from the cell?
No. It removes conditional-formatting rules only, not regular cell formatting such as fill color, font, borders, number formats, or the cell value.
How do I show both the arrow and the cell value in Excel?
Set .ShowIconOnly = False on the IconSetCondition. Set it to True if you want to hide the displayed cell value and show only the arrow icon.




