Conditional formatting for an entire row based on other rows (2007)

**Krushr** · 11-24-2011, 02:52 PM

Hi there, I'm having an issue with a little project I'm working on. I need to highlight certain rows based on the values in one particular column -- the tricky part being that it's based on values in the previous and next column. More specifically, I've got numerical values in column W, and I need to highlight each row where the value in column W is greater than or equal to 0.3, OR the values in the two surrounding rows (same column) are equal to or greater than 0.3. I've got part of it -- I made it so that it highlights the row the value in that row or the next one meet the criteria, but I don't know how to reference the previous row. Here's what I have so far (under "use a formula to determine what cells to format):

=OR($W1>=0.3,$W2>=0.3)

Since W0 isn't a row, how do I reference the previous row?

Thanks!

**NBVC** · 11-24-2011, 03:13 PM

If you must start at row 1, then apply separate CF to row 1..

e.g. =or($W1>=3,$W2>=3)

and for row 2 onwards

=or($W2>=3,$W1>=3,$W3>=3)

**Krushr** · 11-24-2011, 03:25 PM

My understanding is that, if I want to highlight an entire row, I have to select the whole sheet and use the first cell in the column as my reference cell (W1). Also, the criteria state that the previous AND next values must be greater than or equal to 0.3, so this is what I'm trying to do:

=OR($W1>=0.3,AND(W2>=0.3,W0>=0.3))

However, cell W0 doesn't exist so I'm getting an error.

Thanks.

**NBVC** · 11-24-2011, 03:38 PM

Originally Posted by Krushr

My understanding is that, if I want to highlight an entire row, I have to select the whole sheet and use the first cell in the column as my reference cell (W1).

No, not true, you can select from any row down, but your formula needs to reference the top-most row you selected.

So if you select row 1 and apply:

=OR($W1>=0.3,W2>=0.3)

and then select Row 2 down and apply:

=OR($W2>=0.3,AND(W3>=0.3,W1>=0.3))