The Engineered By Design (ebd)

Ever needed to apply automatic formatting to every second row in MS Excel? - But need to have a different formatting based on what the contents of the cell are?

Using the MOD and ROW functions, you can tell Excel to perform tasks on data in every other row. - An aditional condition will make it possible to format the cells additionally based on the content on the cell, using the "Cell Value Is" Option.

Click here for basic tutorial, and general information about the ROW & MOD functions

This method can be extended to become a very powerfull time saver for creating visually appealing, and easily edited spreadsheets very quickly.

(Click On Thumbnails for bigger pictures!)

Method:

1.	Select your data range.	Conditional formatting Box
2.	On the Format menu, click "Conditional Formatting."
3.	Under Condition 1, select "Cell Value Is".
4.	In the data entry box, type”X” - (or what ever data you wish to match & Change formatting for)
5.	Click the Format button. In the Format Cells dialogue box, click the Patterns tab.
6.	Select a colour from the pattern Dialogue, then click OK	Format Box

7.	Under Condition 2, select "Formula Is".
8.	In the data entry box, type”=MOD(ROW(),2)=0”
9.	Click the Format button. In the Format Cells dialogue box, click the Patterns tab.
10	Select a colour from the pattern Dialogue, then click OK
11	Click OK again in the Conditional Formatting box.

The formula in step 8: =MOD(ROW(),2)=0, checks whether the row number divided by 2 has a remainder of 0. If this is true, the row is coloured in. If false the row is left as it is. The condtions are formatted in order, this means that if the first condition is met, the 2nd will not be processed.