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.
Name | Value | Description |
---|---|---|
xl3Arrows | 1 | 3 Arrows |
xl3ArrowsGray | 2 | 3 Arrows Gray |
xl3Flags | 3 | 3 Flags |
xl3Signs | 6 | 3 Signs |
xl3Symbols | 7 | 3 Symbols |
xl3TrafficLights1 | 4 | 3 Traffic Lights 1 |
xl3TrafficLights2 | 5 | 3 Traffic Lights 2 |
xl4Arrows | 8 | 4 Arrows |
xl4ArrowsGray | 9 | 4 Arrows Gray |
xl4CRV | 11 | 4 CRV |
xl4RedToBlack | 10 | 4 Red To Black |
xl4TrafficLights | 12 | 4 Traffic Lights |
xl5Arrows | 13 | 5 Arrows |
xl5ArrowsGray | 14 | 5 Arrows Gray |
xl5CRV | 15 | 5 CRV |
xl5Quarters | 16 | 5 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.