Excel 2010 VBA: Set the color of a cell reference cell in previous column

I have macro that populates a sheet, and copies values into a grid. I want to add conditional formatting to the cells, so that when they are updated if they become greater than the previous column they turn red, smaller they turn green and become 0 they turn grey.

My problem is adding the condition and referenceing the cell.

Set newCell = originalEstimateCell.Offset(0, 2 + i)
newCell.FormatConditions.Add Type:=xlCellValue, Operator:=xlGreater, Formula1:="XX"
newCell.FormatConditions(1).Interior.Color = RGB(255, 0, 0)

My question is what replaces the XX to refer to the previous column in the same row, ie. if we are in Cell C7 开发者_如何学GoI want to refer to cell B7.

Thanks

---

Try this one

Sub test()
    ActiveCell.FormatConditions.Add Type:=xlCellValue, Operator:=xlGreater, _
                      Formula1:="=" & ActiveCell.Offset(0, -1).Address()
    ActiveCell.FormatConditions(1).Interior.Color = RGB(255, 0, 0)    
End Sub

The code above states that the current cell value should be greater than the value of the Formula1, and the formula itself is the address of the cell one column before the active cell. If you want some other cell to be formatted, relatively to the active cell, just change the paramters of the offset method.

I hope it helped.

---

My question is what replaces the XX to refer to the previous column in the same row, ie. if we are in Cell C7 I want to refer to cell C6.

C6 would be the previous row to C7, not the previous column

Try

=INDIRECT("C" & ROW()-1)

to get the value from the previous row