Discussion on:

Use conditional formatting to add shading to rows or columns in an Excel worksheet

I have often wondered how to highlight every second or third row. I've been doing it the hard way, formatting each row one at a time. I played around with this and discovered I don't have to format the entire sheet but can highlight a certain range also.

Thanks Susan,
Barry

Especially useful when new rows are inserted or rows are frequently sorted. Alex

I sometimes like to highlight every other row as a way to make the page easier to read. The formula =mod(row(),2)=0 technique works OK until you filter or hide rows/columns. For example if you set up the formatting to shade every other row, then hide one row that was not highlighted, then you will have two highlighted rows together in the displayed view).

The following is an alternate formatting equation that (as long as there is one row/column that has no blank cells) will ignore hidden rows/columns in the worksheet. My example is for every other row assuming the first column has no blank cells: MOD(SUBTOTAL(103,OFFSET($A$1,0,0,ROW(),1)),2)=0

Cam

I have a situation as following, can somebody help me?

if cell A1= AA, then the whole row change color to red,
if cell A1=AB then the whole row change color to yellow.

Thanks