Automatic Coloring of Odd and Even Rows

From NeoWiki

You can format a spreadsheet to alternate the background color in the rows of a cell range. To do that, you need to create a style for each color and then use conditional formatting.

Contents 1 Creating the Style 2 Conditional Formatting 3 Three Different Colors 4 Every Nth Row

Creating the Style

• Open a new spreadsheet.
• Select a cell.
• Go to the Format menu and select Cells….
• In the Format Cells window that appears, click on the Background tab.
• Choose the color you desire and click OK.
• While this cell is still selected, go to the Format menu and select Styles and Formatting.
• In the Styles and Formatting window, click on the New Style from Selection icon (the second icon from the right).
• In the dialog that opens, give this style a name, for example OddRow.
• Repeat these steps with a second cell and call the style EvenRow.

Conditional Formatting

The conditional formatting is only available if the AutoCalculate function is enabled.

• Go to the Tools menu, select Cell contents then check AutoCalculate if necessary.
• Select the whole page by pressing the Command-A keys, or select only the range you want to be formatted this way.
• Go to the Format menu and select Conditional Formatting….
• In the Conditional Formatting window, in the Condition 1 section, choose Formula is in the drop-down menu and enter

ISODD(ROW())

in the text box.

• Enter OddRow in the drop-down menu of the Cell Style section.
• Check the Condition 2 box.
• Choose Formula is in the drop-down menu and enter

ISEVEN(ROW())

in the text box.

• Enter EvenRow in the drop-down menu of the Cell Style section.
• Click OK.

Note: This will apply automatically if you insert or delete rows in the cell range. However, if you copy/paste cells from outside this range, you will override the formatting. Besides, as the ROW() function relates to the absolute row number, if you hide a row, or apply the AutoFilter function, you'll get two adjacent rows of the same color.

• If you want to use this format in other spreadsheets, save it as a template, by applying the method described in Editing Default Styles and Using Templates in NeoWiki.

Three Different Colors

The conditional formatting allows you to set three conditions. Thus you can apply this method to three rows. Let us suppose that you have created three styles called Row1, Row2 and Row3.

• Select the whole page by pressing the Command-A keys, or only the range you want to be formatted this way.
• Go to the Format menu and select Conditional Formatting….
• In the Conditional Formatting window, in the Condition 1 section, choose Formula is in the drop-down menu and enter the formula

MOD(ROW();3)=1

• Enter Row1 in the drop-down menu of the Cell Style section.
• Check the Condition 2 box.
• Choose Formula is in the drop-down menu and enter the formula

MOD(ROW();3)=2

• Enter Row2 in the drop-down menu of the Cell Style section.
• Check the Condition 3 box.
• Choose Formula is in the drop-down menu and enter the formula

MOD(ROW();3)=0

• Enter Row3 in the drop-down menu of the Cell Style section.
• Click OK.

Every Nth Row

If you want to apply formatting only to every nth row (for instance, every third row), you can use a modification of the above method. Create the style as described above; for this example, let us suppose you have named the style ThirdRow.

• Select the whole page by pressing the Command-A keys, or only the range you want to be formatted this way.
• Go to the Format menu and select Conditional Formatting….
• In the Conditional Formatting window, in the Condition 1 section, choose Formula is in the drop-down menu and enter the formula (where 3 is the nth row you want highlighted)

MOD(ROW();3)=0

• Enter ThirdRow in the drop-down menu of the Cell Style section.
• Click OK.

This will assign the style to every row that is divisible by 3. If you want to apply formatting to every third row but your range begins on a row not divisible by three, or you want to apply formatting to Row 1 and every third row thereafter (1, 4, 7, etc.), set the result of the formula to the appropriate value. MOD returns the remainder when dividing one number by another; MOD(ROW();3) of Row 1 is 1; MOD(ROW();3) of Row 2 is 2. So to apply formatting to Row 1 and every third row thereafter, use MOD(ROW();3)=1 as your formula.

If you want to apply formatting to every fourth row, use 4 as the second value in the MOD function (MOD(ROW();4)) and so forth, supplying the appropriate remainder after the equals sign as needed.