Highlighting Dynamically

Again, select the range A1:H100, ensuring that A1 is the active cell. Select Home → Conditional Formatting → NewRule → "Use a formula to determine which cells to format," and in the "Format values where this formula is true" box (pre-2007, go to Format → Conditional Formatting... → change "Cell Value Is" to "Formula Is"), type the following formula:

=AND(MOD(ROW( ),2),COUNTA($A1:$H1))

Note that you do not reference rows absolutely (with dollar signs), but you do reference columns this way.

Click the dialog's Format option and select the desired formatting, then click OK, and OK again. Any roww ithin the range A1:H100 that does not contain data will not have conditional formatting applied. If you remove data from a specific rowin your table, it too will no longer have conditional formatting applied. If you add new data anywhere within the range A1: H100, the conditional formatting will kick in.

This works because when you supply a formula for conditional formatting, the formula itself must return an answer of either TRUE or FALSE. In the language of Excel formulas, 0 has a Boolean value of FALSE, while any nonzero value has a Boolean value of TRUE. When you use the formula =MOD(ROW( ),2), it will return either a value of 0 (FALSE) or a number greater than 0.

The ROW( ) function is a volatile function that always returns the row number of the cell it resides in. You use the MOD function to return the remainder after dividing one number by another. In the case of the formula you used, you are dividing the rownumber by 2, so all even rownumbers will return 0, while all odd row numbers will always return a number greater than 0.

When you nest the ROW( ) function and the COUNTA function in the AND function, it means you must return TRUE (or any number greater than 0) to both the MOD function and the COUNTA function for the AND function to return TRUE. Note that COUNTA counts all nonblank cells.