Making Banded Rows Dynamic

In the last post we used conditional formatting to fill alternating rows in a range of rows.  However, there may be occasions when you want to use banded rows but only if the rows contain data.  This can be accomplished by changing the formula previously used.


For Excel 2007 select a cell in the range previously used and then from the Home tab select Conditional Formatting > Manage Rules > Edit Rule and change the formula to =AND(MOD(ROW(),2),COUNTA($A1:$E1)).

Click OK and the Apply


For earlier versions of Excel click Format > Conditional Formatting and change the formula as above

Click OK


Now the cells in your selected range are filled only when an entry is made in any cell in the odd numbered rows.

As always if you have any questions or comments you can enter them as a comment here on the blog or email me at the office.