Automatic Coloring of Odd and Even Rows

From NeoWiki

(Difference between revisions)
Jump to: navigation, search
Revision as of 20:26, 30 January 2008 (edit)
Jgd (Talk | contribs)
(Conditional Formatting - add note about copying/pasting and hiding rows (from yoxi's remark))
← Previous diff
Revision as of 20:33, 30 January 2008 (edit) (undo)
Jgd (Talk | contribs)
(Conditional Formatting -add AutoFilter)
Next diff →
Line 25: Line 25:
*Enter {{prefName|EvenRow}} in the drop-down menu of the {{section|Cell Style}} section. *Enter {{prefName|EvenRow}} in the drop-down menu of the {{section|Cell Style}} section.
*Click {{button|OK}}. *Click {{button|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 you'll get two adjacent rows of the same color.+:'''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. *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.

Revision as of 20:33, 30 January 2008

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.

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

  • 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.


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.


This article in other languages: Français
Personal tools