Forum Discussion

PCThomson's avatar
PCThomson
Copper Contributor
Jan 10, 2024

Error Adding IconSets using VBA

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:

 

 

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.

developer
Excel
Macros and VBA
  • HansVogelaar's avatar
    HansVogelaar
    MVP
    Jan 10, 2024

    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

Resources