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

Question

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?

Solution

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

Comments

[Tim Williams]

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]

@TimWilliams: Sorry didn't see your comment. The crossover was unintentional.

[David Kin]

Also thanks Tim for your suggestion, today don't get time to try your solution. I will try it later.

[David Kin]

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]

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]

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.