Skip to content

Excel VBA – Apply Icon Set Conditional Formatting with VBA Macro

    Apply Icon Set Conditional Formatting with VBA Macro

    To add Icons with conditional formatting use the AddIconSetCondition method of the FormatConditions object.  With the IconSet property you can then specify which icon set to use – see the table of VBA constants below.

    NameValueDescription
    xl3Arrows13 Arrows
    xl3ArrowsGray23 Arrows Gray
    xl3Flags33 Flags
    xl3Signs63 Signs
    xl3Symbols73 Symbols
    xl3TrafficLights143 Traffic Lights 1
    xl3TrafficLights253 Traffic Lights 2
    xl4Arrows84 Arrows
    xl4ArrowsGray94 Arrows Gray
    xl4CRV114 CRV
    xl4RedToBlack104 Red To Black
    xl4TrafficLights124 Traffic Lights
    xl5Arrows135 Arrows
    xl5ArrowsGray145 Arrows Gray
    xl5CRV155 CRV
    xl5Quarters165 Quarters

    You specify criteria for each traffic light as you would in Excel (see image below) using the IconCriteria property. For this example we are using three traffic lights.  The red traffic light will look after itself.  The amber traffic light criteria can be specified with IconCriteria(2) and the green icon with IconCriteria(3).  For the criteria specify Type as shown in the table below (not all Types are applicable), Operator as XLGreater or XLGreaterEqual  and then Value, which in our case is a formula.

    XlConditionValueTypes
    xlConditionValueAutomaticMax
    xlConditionValueAutomaticMin
    xlConditionValueFormula
    xlConditionValueHighestValue
    xlConditionValueLowestValue
    xlConditionValueNone
    xlConditionValueNumber
    xlConditionValuePercent
    xlConditionValuePercentile

    Sub iconsets()
    Range("AC3").Name = "Target"
    Range("AC4").Name = "StopProduct"
    Dim rg As Range
    Dim iset As IconSetCondition
    Set rg = Range("Z6", Range("Z6").End(xlDown))
    rg.FormatConditions.Delete
    Set iset = rg.FormatConditions.AddIconSetCondition
    'select the traffic lights iconset
    With iset
        .IconSet = ActiveWorkbook.iconsets(xl3TrafficLights1)
        .ReverseOrder = False
        .ShowIconOnly = False
    End With
    'specify amber traffic light for values >= 80% of target(2500)
    With iset.IconCriteria(2)
        .Type = xlConditionValueFormula
        .Operator = xlGreaterEqual
        .Value = "=Target*StopProduct"
    End With
    'specify green traffic light for values >= the target(2500)
    With iset.IconCriteria(3)
        .Type = xlConditionValueFormula
        .Operator = xlGreaterEqual
        .Value = "=Target"
    End With
    End Sub

    The code above relates to the data shown below.