How to Customize Condition Icons
Arthur
I want to customize icon standard. I need 5 conditions. I used IconSetCondition.
requirement is:
- value < 0.85
- value < 0.95
- Value < 1.06
- Value < 1.15
- value >= 1.15
I want to use 3Traffic lights in this way:
- value <0.85 -> "red traffic light"
- value < 0.95 -> "orange traffic light"
- value <1.06 -> "green traffic light"
- value <1.15 -> "orange traffic light"
- value >= 1.15 -> "red traffic light"
I volunteered to use xl5Arrows to get 5 conditions.
Set r = Range(Cells(3, 6), Cells(nbtopics + 2, 6))
With r
With .FormatConditions
.Delete
.AddIconSetCondition
End With
With .FormatConditions(1)
.SetFirstPriority
.ReverseOrder = False
.ShowIconOnly = False
.IconSet = ActiveWorkbook.IconSets(xl5Arrows)
With .IconCriteria(2)
.Type = xlConditionValueNumber
.Value = 0.85
.Operator = xlGreater
End With
With .IconCriteria(3)
.Type = xlConditionValueNumber
.Value = 0.95
.Operator = xlGreater
End With
With .IconCriteria(4)
.Type = xlConditionValueNumber
.Value = 1.05
.Operator = xlGreater
End With
With .IconCriteria(5)
.Type = xlConditionValueNumber
.Value = 1.15
.Operator = xlGreater
End With
End With
End With
This code works, but I want to Traffic lights
replace it xl5Arrows
.
Thank you for your help. Feel free to comment if you need more details.
Sterilize
As Mark S. mentioned , there is no default enumerationxl5trafficlights
xlIconSet
However, you're setting that up .Type
, .Value
and .Operator
your IconCriteria - just make sure to also set up .Icon
at the same time. This will be set IconSetCriteria
to xlCustomSet
.
This is a "circle" from which you can build a set of 5 traffic lights like this:xlIcon
xlIconBlackCircleWithBorder
xlIconGrayCircle
xlIconGreenCircle
xlIconRedCircleWithBorder
xlIconPinkCircle
xlIconYellowCircle
xlIconGreenCheckSymbol
xlIconRedCrossSymbol
xlIconYellowExclamationSymbol
xlIconWhiteCircleAllWhiteQuarters
E.g
With .IconCriteria(1)
.Icon = xlIconWhiteCircleAllWhiteQuarters
End With
With .IconCriteria(2)
.Icon = xlIconGreenCircle
.Type = xlConditionValueNumber
.Value = 0.85
.Operator = xlGreater
End With
With .IconCriteria(3)
.Icon = xlIconYellowCircle
.Type = xlConditionValueNumber
.Value = 0.95
.Operator = xlGreater
End With
With .IconCriteria(4)
.Icon = xlIconRedCircleWithBorder
.Type = xlConditionValueNumber
.Value = 1.05
.Operator = xlGreater
End With
With .IconCriteria(5)
.Icon = xlIconBlackCircleWithBorder
.Type = xlConditionValueNumber
.Value = 1.15
.Operator = xlGreater
End With