VBA & Macro Problems

Fix Excel VBA Icon Set Conditional Formatting with Left-Cell Values

Algirdas JasaitisAlgirdas Jasaitis Nov 12, 2026 868 views

Question details

Apply a three-arrow Excel icon set through VBA that compares each target cell with the value immediately to its left.

Fix Excel VBA Icon Set Conditional Formatting with Left-Cell Values
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.
Before you start

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.

Solution 1Recommended

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.

1
Open the VBA editor

In Excel, press Alt+F11, then select Insert > Module to create or open a standard VBA module.

2
Add the icon-set macro

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

3
Adjust the worksheet range

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.

4
Run the macro

Place the cursor inside UpdateIconSets and press F5, or return to Excel and choose Developer > Macros > UpdateIconSets > Run.

Set three-arrow icon criteria 2 and 3 in reverse order
Dynamic left-cell comparison enabled: Using Cells(row, col).Offset(0, -1) makes each icon-set rule compare against the value immediately to the left instead of a fixed numeric threshold.
Free Microsoft Office alternative

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.

Use spreadsheet conditional-formatting tools in a familiar Excel-style worksheet environment.Open and save Microsoft Excel .xlsx, .xls, and .xlsm workbook formats.Work with a lightweight, free office suite for everyday spreadsheet editing.Move existing Office documents to WPS Office with a familiar ribbon-based interface.
QA img-9

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.