Conditional Formatting with Custom Formula referencing the cell itself

Question

I have a google spreadsheet which has columns for each day (with the date in the top cell) and in each cell a string indicating status.

I need to apply conditional formatting to cells based on the criteria of the date of the column being in the past AND the text inside the cell starting with the substring "OK".

It seems that I must use a Custom Formula in order to deal with the date part, but then how would I reference the content of the cell itself? I've tried referencing the cell using CELL("contents",ADDRESS(ROW(),COLUMN()) but this returns an Error: Argument must be a range.

Answer 1

With conditional formatting (both in Excel and google docs) you just use the formula that applies to the top left cell of the range....and you can simply refer to the cell by it's cell reference, so assuming dates are in B1:J1 and your status data is in B2:J100 then select that latter range and (in conditional formatting) apply the formula that applies to B2, i.e.

=AND(B$1<TODAY(),LEFT(B2,2)="OK")

This will work for the whole range

You must use absolute/relative references as if you were copying the formula down/across the range, so you need the $ in B$1 because you want every row to refer to row 1....although going across you want it to change to C$1, D$1 etc. hence no $ before the column letter