VBA conditional formating -- custom icon set

[Johnnyboy1x]

Hello All --

As a step in my VBA code for creating/formatting a KPI report, I want to apply conditional formatting.

I am attempting to use the traffic light icon set, but I modify its colors to RED, GREEN, RED.

The code is below and works in the macro as far as setting conditions (values and numbers) when I run it, but the cells icons default back to GREEN, YELLOW, RED once macro completes.

Any advice on how to approach this?

     Columns("O:O").Select    Selection.FormatConditions.AddIconSetCondition
    Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
    With Selection.FormatConditions(1)
        .ReverseOrder = False
        .ShowIconOnly = False
        .IconSet = ActiveWorkbook.IconSets(xl3TrafficLights1)
    End With
    With Selection.FormatConditions(1).IconCriteria(2)
        .Type = xlConditionValueNumber
        .Value = -30
        .Operator = 7
    End With
    With Selection.FormatConditions(1).IconCriteria(3)
        .Type = xlConditionValueNumber
        .Value = 30
        .Operator = 7
    End With

Thanks in advance for any help,

Johnyboy1x

 

[Johnnyboy1x]

Hello All --

Just wanted to provide an update. We have solved this problem with the code below.

Thanks, Johnnyboy1x

   Columns("O:O").Select    Selection.FormatConditions.AddIconSetCondition
    Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
    With Selection.FormatConditions(1)
        .ReverseOrder = False
        .ShowIconOnly = False
        .IconSet = ActiveWorkbook.IconSets(xl3TrafficLights1)
    End With
    With Selection.FormatConditions(1).IconCriteria(2)
        .Icon = xlIconGreenCircle
        .Type = xlConditionValueNumber
        .Value = -30
        .Operator = 7
    End With
    With Selection.FormatConditions(1).IconCriteria(3)
        .Icon = xlIconRedCircle
        .Type = xlConditionValueNumber
        .Value = 31
        .Operator = 7
    End With