title: How to use FormatConditions to change the cell color based on the compare of cell value with other cell?
body: <p>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.</p>
  
  <p>I used formatConditions implemented some code, but the result is not very correct.</p>
  
  <pre><code> 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 
  </code></pre>
  
  <p>With this code , I got the result rule is: Cell Value > 18 (18 is the cell value of D6)</p>
  
  <p>But what I want is rule like: Cell value > $D6</p>
  
  <p>Anyone can help?</p>