XL2007 Conditional Formatting

Ziggy12

Active Member
Joined
Jun 26, 2002
Messages
365
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
 

Excel Facts

Get help while writing formula
Click the italics "fx" icon to the left of the formula bar to open the Functions Arguments dialog. Help is displayed for each argument.
Hi Ziggy,

If you want the formatting to be based on comparison to another cell's value,
you'll want to use Type=xlConditionValueFormula instead of xlConditionValueNumber.

This will make a CF rule in which...
Red = Formatted Cell is greater than $B$1
Orange = Formatted Cell equals $B$1
Green = Formatted Cell is less than $B$1

Code:
Sub CreateIconSetCF2()
    Dim cfIconSet As IconSetCondition
    With Range("C1:C12")
        .FormulaR1C1 = "=Row()"
        .Value = .Value
        Set cfIconSet = .FormatConditions.AddIconSetCondition
    End With
    With cfIconSet
        .ReverseOrder = True
        .ShowIconOnly = False
        .IconSet = ActiveWorkbook.IconSets(xl3TrafficLights2)
        With .IconCriteria(2)
            .Type = xlConditionValueFormula
            .Value = "=$B$1"
            .Operator = 7
        End With
        With .IconCriteria(3)
            .Type = xlConditionValueFormula
            .Value = "=$B$1"
            .Operator = 5
        End With
    End With
End Sub
 
Upvote 0
So close
The formatting is determined of cell $B$1 which has to be an absolute reference - errors if its changed to relative reference which means each cell has to have its own formatting set individually.

Maybe for each cell in range kind of thing rather than selecting the range.

ziggy
 
Upvote 0
So close
The formatting is determined of cell $B$1 which has to be an absolute reference - errors if its changed to relative reference which means each cell has to have its own formatting set individually.

I was hoping you wanted to compare all the values in C1:C12 to $B$1 instead of same row of B. ;)

Yes, that is a known limitation of CF Icon Sets, you can't use relative references in the formula.

Since you are applying the CF with VBA, it won't be to hard to make a separate CF for each Cell.

Good luck!
 
Upvote 0

Forum statistics

Threads
1,226,287
Messages
6,190,066
Members
453,592
Latest member
bcexcel

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top