mike[space] dot net

Warning, geekery ahead ...

I've just been putting together a spreadsheet to get a handle on the props we need for Out Of Office. It had become quite unreadable because of the length, so decided that alternate row shading was required. A quick google led me to the Microsoft KB page, and I had a nice set of alternating rows. But I wanted the alternate shading to only show up if there was content. The tip from MS didn't seem to be working, as some cells were shaded, some were not, but it appeared to have nothing to do with the row it was in.

Having worked out the problem, I thought it might be useful to share. The formula from MS reads as:

=AND(MOD(ROW(),2)=0,A1<>"")

which works if your data starts at A1, and all the columns of the rows are going to be populated with data. But my sheet doesn't fit either of these criteria. In fact, the key column is B, not A (i.e. I consider it a legitimate data row if there is data in B), and the data rows start at row 5. So, having selected all the rows down to the bottom of the entire spreadsheet, the formula should be:

=AND(MOD(ROW(),2)=0,$B5<>"")

This means that as the conditional formatting is applied to all the cells, it always checks column B ($ fixes the reference, without it Excel dynamically shifts across the columns as they get applied), and it will be looking at the right row number.

Hopefully that's clear, and is helpful for somebody ;-)