How to use FormatConditions to change the cell color based on the compare of cell value with other cell?

excel vba

37,002

This is the method I used (you can easily create and modify one using the Macro Recorder). The formatting will be applied to the seventh column ("G"). The formula is self-explanatory. Note that since the formula is a string you can concatenate the columns / rows dynamically.

Dim r As Range

Set r = Sheet1.Columns(7)
r.FormatConditions.Add Type:=xlExpression, Formula1:="=$G1>$D1"
r.FormatConditions(r.FormatConditions.Count).SetFirstPriority
With r.FormatConditions(1)
    .Interior.PatternColorIndex = xlAutomatic
    .Interior.ColorIndex = 19
    .Font.ColorIndex = 26
End With
r.FormatConditions(1).StopIfTrue = False

set r = nothing

37,002

David Kin

Updated on February 26, 2020

Comments

David Kin about 4 years
I need to change the cell color if the cell value is bigger than another cell in other column. for example value in G6 > D6, and this rule need to apply to the whole column.

I used formatConditions implemented some code, but the result is not very correct.
```
 Set rngCell = Cells(6, 7)

 Set objCF = rngCell.FormatConditions.Add _
            (Type:=xlCellValue, Operator:=xlGreater, Formula1:=rngCell.offset(, -3))
'set formats for new CF
With objCF
    .Font.ColorIndex = 26
    .Interior.ColorIndex = 19
End With 
```
With this code , I got the result rule is: Cell Value > 18 (18 is the cell value of D6)

But what I want is rule like: Cell value > $D6

Anyone can help?
- Tim Williams about 12 years
  
  You need to use Type:=xlExpression. Also, see this link for an issue you'll likely run into support.microsoft.com/kb/895562
- Siddharth Rout about 12 years
  
  @TimWilliams: Sorry didn't see your comment. The crossover was unintentional.
- David Kin almost 12 years
  
  Also thanks Tim for your suggestion, today don't get time to try your solution. I will try it later.
David Kin almost 12 years

Thanks for your answer, but I'm still trying... using this way, the rule I generated is something like :=L1048557>I1048557 , I don't know why, what I want to compare is the G and D column.
David Kin almost 12 years

Can I generate the rule like: Cell Value > $D6, then I can apply this rule to all the column, because it works well if I manually add this rule to my worksheet.
David Kin almost 12 years

Thanks for your input, I tried, but using this the format of whole column will change if the condition is true. this is not what I expect.