Wednesday, March 21, 2018

Apply traffic light icons to a custom range of cells in Excel using VBA


Sub iconsets0()
Dim rg As Range
Dim iset As IconSetCondition
Dim positiveCells As Range
Dim negativeCells As Range
'observed range:
Set rg = Range("C7", "G25")
rg.FormatConditions.Delete
For Each c In rg
    If c.Value >= 0 Then
        If Not positiveCells Is Nothing Then
            Set positiveCells = Union(positiveCells, c)
        Else
            Set positiveCells = c
        End If
    End If
    If c.Value < 0 Then
        If Not negativeCells Is Nothing Then
            Set negativeCells = Union(negativeCells, c)
        Else
            Set negativeCells = c
        End If
    End If
Next c

' apply to cells with positive number values:
Set positiveSet = positiveCells.FormatConditions.AddIconSetCondition

With positiveSet
    .IconSet = ActiveWorkbook.iconsets(xl3TrafficLights1)
    .ReverseOrder = True
    .ShowIconOnly = False
End With
With positiveSet.IconCriteria(2)
    .Type = xlConditionValueNumber
    .Operator = xlGreaterEqual
    .Value = "10"
End With
With positiveSet.IconCriteria(3)
    .Type = xlConditionValueNumber
    .Operator = xlGreaterEqual
    .Value = "25"
End With

' apply to cells with negative number values:
Set negativeSet = negativeCells.FormatConditions.AddIconSetCondition

With negativeSet
    .IconSet = ActiveWorkbook.iconsets(xl3TrafficLights1)
    .ReverseOrder = False
    .ShowIconOnly = False
End With
With negativeSet.IconCriteria(2)
    .Type = xlConditionValueNumber
    .Operator = xlGreaterEqual
    .Value = "-25"
End With
With negativeSet.IconCriteria(3)
    .Type = xlConditionValueNumber
    .Operator = xlGreaterEqual
    .Value = "-10"
End With


End Sub