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
Wednesday, March 21, 2018
Apply traffic light icons to a custom range of cells in Excel using VBA
Subscribe to:
Post Comments (Atom)
No comments:
Post a Comment