1

I'm trying to figure out the proper conditional formatting formula to hilight a cell based on the date in that cell (and/or an adjascent one) representing the most recent date in the column.

Given data like this:

1/1/2011    v1
2/2/2015    v2
4/1/2010    c2
2/2/2015    d5

it would highlight rows 2 and 4, and optionally both columns A and B.

So basically the idea is a conditional format if the date in column A= the latest date of the range A1:A4

I'd like to see a variation that will work on just a single column or multiple columns based on the date in one column.

1

Apply to range A1:B conditional formatting based on custom formula

=$A1=MAX($A$1:$A)

The formula =X=Y returns 1 (true) is X=Y, and 0 (False) otherwise. So, formatting is applied (to both A and B) if the value in A column is equal to the maximal value in A column, as you wanted.

The dollar signs (absolute references) are needed so that as the formatting propagates throughout both columns, the references are still made to column A.

If there is a header row, adjust accordingly, applying

=$A2=MAX($A$2:$A)

to the range A2:B.

You can make this work on a single column or more than two columns: just change the range of formatting. The formatting formula should be written as it is to be applied to the upper left corner of the range.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.