Cell formats need to change with changing cell value

Question

I have the following code that I use to apply conditional formatting to a worksheet. The problem is, if I vary the value in Formula1:=Cells(11,7) [for example], the formatted data does not reflect the updated change. When I open the Excel's Conditional Formatting Rules Manager, the rules do not show a reference to the actual cell, but the value of the cell at the time that I ran the macro. Any thoughts?

Dim i As Integer

For i = 7 To 13
    Range(Cells(21, i), Cells(118, i)).Select
    Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlGreater, _
        Formula1:=Cells(11, i)

    With Selection.FormatConditions(1).Interior
        .PatternColorIndex = xlAutomatic
        .Color = 255
        .TintAndShade = 0
    End With


    Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlLess, _
        Formula1:=Cells(13, i)

    Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
    With Selection.FormatConditions(1).Interior
       .PatternColorIndex = xlAutomatic
       .ColorIndex = 6
       .TintAndShade = 0
    End With
Next

You don't need to loop. Check `ModifyAppliesToRange` method like what I did in these posts [1](https://stackoverflow.com/a/23752853/2685412) and [2](https://stackoverflow.com/a/25320881/2685412). You can also check how I explained it [here](https://stackoverflow.com/a/23307005/2685412). — L42, Jan 31 '18 at 02:00
Does that work for reference values that are unique to each column? For 6 columns it will be 12 rules. — Quinn Haynie, Jan 31 '18 at 02:03
Seeing your sample code, it should. But again that doesn't answer your actual question. I just thought you might want to consider a simpler approach. — L42, Jan 31 '18 at 02:07

Tim Williams · Accepted Answer · 2018-01-31T05:23:55.927

1

What you want is something more like Formula1:="=$G$11" so:

Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlLess, _
        Formula1:="=" & Cells(11, i).Address()

edited Jan 31 '18 at 05:23

answered Jan 31 '18 at 01:59

Tim Williams

154,628
8
97
125

This works and does exactly what I was asking for. Thanks! – Quinn Haynie Jan 31 '18 at 02:04

score 0 · Answer 2 · answered Jan 31 '18 at 01:55

0

Use code like this to set the Formula1 property.

Formula1:="=Cells(11, " & i & ")"

answered Jan 31 '18 at 01:55

Variatus

14,293
2
14
30

Thanks, that is getting closer, now the output in Excel shows the rule as being based on the Cells(R,C) value. Is there a way to convert it to $A$1 somehow? – Quinn Haynie Jan 31 '18 at 02:01

Cell formats need to change with changing cell value

2 Answers2