Conditional formatting of rows depending on the value of adjacent cells

**griffindog** · 06-19-2013, 08:06 PM

I would like to format the entire row based on what is in Column A.

In column A, I have the days of the week, in order, with sometimes repeating values. See below:

A1 Sunday
A2 Monday
A3 Tuesday
A4 Wednesday
A5 Thursday
A6 Friday
A7 Saturday
A8 Sunday
A9 Monday
A10 Monday
A11 Monday
A12 Tuesday
A13 Wednesday
A14 Thursday
A15 Thursday
A16 Friday

What I want to do is format the row with Monday, but if there are multiple Mondays in a row, only the last instance. So in the example above, the formatting would only be applied to row 2 and row 11.

The formula I used to use was this, but when there are multiple Mondays in a row it doesn't help me.
=INDIRECT("A"&ROW())="Monday"

So I am stuck. Please help, thanks!

**benishiryo** · 06-19-2013, 08:33 PM

hi there. do not highlight the entire row. that will take up memory. select the range you want to apply to (say from A1:E20)
in Excel 2007 & above, go to Home -> Conditional Formatting -> New Rule -> Use a formula to determine which cells to format -> Format values where this formula is true:
=LOOKUP(2,1/($A$1:$A$16="Monday"),ROW($A$1:$A$16))=ROW()