Excel 2007 Column Offset subtraction in Conditional Formatting

Question

The following formula in conditional formatting is not behaving the same as it does in a cell of a worksheet:

 IF(OFFSET(INDIRECT("RC",FALSE),0,4-COLUMN())="Answer Changed",TRUE,FALSE)

I am applying this formula to 3 columns of cells, and need it to look at the 4th column of each respective row. In a cell in a worksheet this works as expected. Is there some trick to this, as I know that the row and column functions work in other conditional formatting formula's I use (i.e., IF(MOD(ROW(),2)=1,TRUE,FALSE))

Answer 1

If I understand correctly, you want columns A through C to be formatted if column D contains "Answer Changed." If that that's the case, and assuming your data starts in Row 2, you can accomplish this by picking cells A2:C2 and entering this conditional formatting formula:

=$D2="Answer Changed"

Then just copy it down as far as needed.

Note that you don't need to use an IF statement. Condtional formatting evaluates whether the statement is TRUE and applies the formatting if it is.