Copilot for Microsoft 365 Tech Accelerator
Feb 28 2024 07:00 AM - Feb 29 2024 10:30 AM (PST)
Microsoft Tech Community

Error Adding IconSets using VBA

Copper Contributor

For some reason I am unable to overcome this issue. I have a Range(B3:L12) that contains numbers between 1 and 9 inclusive. I would like to add conditional formatting to the Range(C3:L12) using 3 Arrows (Green Up, Yellow Side and Red Down) based on whether the cell value is either greater then, equal to or less than the value from the cell to the left. Here's my code:

 

 

Sub UpdateIconSets()
Dim row As Integer, col As Integer
Dim cfIconSet As IconSetCondition
For row = 3 To 12
For col = 3 To 12
Cells(row, col).Select
Selection.FormatConditions.Delete
Set cfIconSet = Selection.FormatConditions.AddIconSetCondition
With cfIconSet
.IconSet = ActiveWorkbook.IconSets(xl3Arrows)
.ReverseOrder = False
.ShowIconOnly = False
With .IconCriteria(1)
.Icon = xlIconGreenUpArrow
.Type = xlConditionValueNumber
.Operator = xlGreater
.Value = Cells(row, col).Offset(0, -1)
End With
With .IconCriteria(2)
.Icon = xlIconYellowSideArrow
.Type = xlConditionValueNumber
.Operator = xlGreaterEqual And xlLessEqual
.Value = Cells(row, col).Offset(0, -1)
End With
With .IconCriteria(3)
.Icon = xlIconRedDownArrow
.Type = xlConditionValueNumber
.Operator = xlLess
.Value = Cells(row, col).Offset(0, -1)
End With
End With
Next col
Next row

End Sub

 

 

You'll see the line that causes the error every time and for which I cannot find a fix:

 

PCThomson_0-1704897985091.png

 

I have tried removing the line, changing the order of the lines, changing it to xlConditionValueFormula with .Value = ">" & Cells(row, col).Offset(0, -1) etc.. but no joy.

 

I can manually create what I am after but have to enter a hard number in the wizard and I need this to be a variable.

2 Replies

@PCThomson 

You have to specify the criteria in reverse order, and only #3 and #2. #1 follows automatically from those.

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

@Hans Vogelaar Excellent thank you so much.