Friday, October 22, 2004

Excel Conditional Formatting Part 2

I know I said i would do this yesterday but I didn't. So sue me.

Following is a more advanced use of conditional formatting.

Alternate row shading.
Select all the cells you want to apply alternate row (or column) shading to
Select Format - Conditional Formatting from the menu.
Select Formula is from the Condition box.
In the formula box type =MOD(ROW(),2)=0 (or =MOD(COLUMN(),2)=0 for columns)
Click the format button and choose a pattern for the shading you want.
Click OK twice and shading will be applied.
To apply an alternate shading in the other row create a second condition by clicking the Add button.
Use this formula =MOD(ROW(),2)<>0 (or =MOD(COLUMN(),2)<>0 for columns)

How this works. The MOD function returns the remainder after dividing a number by an integer. Eg Divide 7 by 3 and the remainder is 1. In this case dividing the row number by 2 returns a remainder of 1 in an odd numbered row. So the first conditon will colour all even numbered rows. The second formula will colour all rows that return a remainder or odd numbered rows.


Posted by: Jethro | 2:06 PM |