[RESOLVED] Automating Conditional Formatting

[Mitch_s_s]

hi,

I've searched as mush as possible (google etc), but maybe my search terms aren't accurate enough

anyway I have 400+ cells that I am looking to apply conditional formatting to

Unfortunately I'm looking to use the icon set formatting (traffic lights)
and because of this, relative referencing is not allowed - the format will be applied based on the current cells value in relation to another cell in the worksheet
so for each cell I need to apply the conditional formatting individually

I've tried recording a macro whilst doing this, but the macro does not capture anything.

Does anyone have any code snippets that I could use, to loop through these cells and apply the formatting?

Thanks

Mitch

[Siddharth Rout]

Unfortunately I'm looking to use the icon set formatting (traffic lights)

Sorry, I didn't quite catch this...

[Mitch_s_s]

when you go in to conditional formatting

then format based on cell value (top option)

within there you can set traffic lights for red amber green

I'm looking to automate this process, but cant suss it!

hope this makes sense

[Siddharth Rout]

Are you using Office 2007?

[Mitch_s_s]

sorry mate yeah office 2007

[Siddharth Rout]

sorry mate yeah office 2007

That's OK

I believe and I could be wrong but You cannot use relative references in Conditional Formatting criteria for color scales, data bars, and icon sets.

If you can tell me exactly what are you trying (perhaps with a sample sheet) then maybe we can try an alternative, i.e if there is one ...

[Mitch_s_s]

You're right

Thats why I'm looking to automate it

I have two sheets identical in structure, one is Quarter 2 and the other Quarter 3

I want to compare quarter 3 with Q2 to see of there is any change in values

as I have over 400 cells, I dont want to set up the conditional formatting on each cell individually

e.g.

if sheet Q3 cell A1 > sheet Q2 cell A1 then green traffic light

[Mitch_s_s]

Sorted it

Let me know what you think

Sub condf()
Dim selcell As Range
Dim cell As Range

For Each cell In Selection
    cell.FormatConditions.AddIconSetCondition
    cell.FormatConditions(cell.FormatConditions.Count).SetFirstPriority
    With cell.FormatConditions(1)
        .ReverseOrder = False
        .ShowIconOnly = False
        .IconSet = ActiveWorkbook.IconSets(xl3ArrowsGray)
    End With
    With cell.FormatConditions(1).IconCriteria(2)
        .Type = xlConditionValueFormula
        .Value = _
        "=HLOOKUP(" & _
         Range("$" & ColumnLetter(cell.Column) & "$3").Address _
         & ",Q1_SCORECARD,MATCH(" & cell.Offset(0, 1 - cell.Column).Address & ",Q1_PRACTICE_LIST,0),FALSE)"
        .Operator = 7
    End With
    With cell.FormatConditions(1).IconCriteria(3)
        .Type = xlConditionValueFormula
        .Value = _
        "=HLOOKUP(" & _
         Range("$" & ColumnLetter(cell.Column) & "$3").Address _
         & ",Q1_SCORECARD,MATCH(" & cell.Offset(0, 1 - cell.Column).Address & ",Q1_PRACTICE_LIST,0),FALSE)"
        .Operator = 5
    End With
Next cell
End Sub