Hi there
Trying to set the conditional formatting colour based on a cells value but haven't quite fiqured out what controls the colours. Have been experimenting with code from help below.
What values control the colour. I want either red if cell C1 is greater than B1, Green if cell C1 is less than B1.
Be interested to know how the orange works as well as I'll probably need that in the future.
Sub CreateIconSetCF()
Dim cfIconSet As IconSetCondition
'Fill cells with sample data from 1 to 10
With ActiveSheet
.Range("C1") = 1
.Range("C2") = 2
.Range("C3") = 3
.Range("C4") = 4
.Range("C5") = 5
.Range("C6") = 6
.Range("C7") = 7
.Range("C8") = 8
.Range("C9") = 9
.Range("C10") = 10
.Range("C11") = 11
.Range("C12") = 12
End With
Range("C1:C12").Select
'Create an icon set conditional format for the created sample data range
Set cfIconSet = Selection.FormatConditions.AddIconSetCondition
'Change the icon set to a five-arrow icon set
cfIconSet.IconSet = ActiveWorkbook.IconSets(xl3TrafficLights2)
'The IconCriterion collection contains all IconCriteria
'By indexing into the collection you can modify each criterion
With cfIconSet.IconCriteria(2)
.Type = xlConditionValueNumber
.Value = 8
.Operator = 7
End With
With cfIconSet.IconCriteria(3)
.Type = xlConditionValueNumber
.Value = 2
.Operator = 7
End With
End Sub
Trying to set the conditional formatting colour based on a cells value but haven't quite fiqured out what controls the colours. Have been experimenting with code from help below.
What values control the colour. I want either red if cell C1 is greater than B1, Green if cell C1 is less than B1.
Be interested to know how the orange works as well as I'll probably need that in the future.
Sub CreateIconSetCF()
Dim cfIconSet As IconSetCondition
'Fill cells with sample data from 1 to 10
With ActiveSheet
.Range("C1") = 1
.Range("C2") = 2
.Range("C3") = 3
.Range("C4") = 4
.Range("C5") = 5
.Range("C6") = 6
.Range("C7") = 7
.Range("C8") = 8
.Range("C9") = 9
.Range("C10") = 10
.Range("C11") = 11
.Range("C12") = 12
End With
Range("C1:C12").Select
'Create an icon set conditional format for the created sample data range
Set cfIconSet = Selection.FormatConditions.AddIconSetCondition
'Change the icon set to a five-arrow icon set
cfIconSet.IconSet = ActiveWorkbook.IconSets(xl3TrafficLights2)
'The IconCriterion collection contains all IconCriteria
'By indexing into the collection you can modify each criterion
With cfIconSet.IconCriteria(2)
.Type = xlConditionValueNumber
.Value = 8
.Operator = 7
End With
With cfIconSet.IconCriteria(3)
.Type = xlConditionValueNumber
.Value = 2
.Operator = 7
End With
End Sub