Ask Your Question

Revision history [back]

click to hide/show revision 1
initial version

Hello,

Solution 1: Reusable - Define your own Auto format

  • Select range A1:D1 and set the color (e.g. white)
  • Select range A2:D2 and set the alternate color (e.g. grey)
  • Select range A3:D3 and set the color (e.g. white - step 1 needs to repeated here)
  • Select range A2:D4 (important to have on uncoolorized line)
  • Go to menu Format -> AutoFormat Styles
  • Click Add - button, and deselect every option but not Pattern
  • Provide a Name (e.g. AltWhiteGrey)
  • Click button OK

Now you can use this Autoformat for any arbitray range (Selecting the range) and using Format -> AutoFormat Styles

Solution 2: - Use Conditional formatting

  • Define a cell style with white background color (e.g. BGWhite)
  • Define a cell style with grey background color (e.g. BGGrey)
  • Add a conditional format using
    • Condition 1: MOD(ROW(),2)=1 - Apply Style 1 (BGWhite)
    • Condition 2: MOD(ROW(),2)=1 - Apply Style 2 (BGGrey)
    • Cell Range: Add here the cells, where you want to apply the format

Drawback of this solution: Conditional Formats take precedence over direct formats.

Hello,

Solution 1: Reusable - Define your own Auto format

  • Select range A1:D1 and set the color (e.g. white)
  • Select range A2:D2 and set the alternate color (e.g. grey)
  • Select range A3:D3 and set the color (e.g. white - step 1 needs to repeated here)
  • Select range A2:D4 (important to have on uncoolorized line)
  • Go to menu Format -> AutoFormat Styles
  • Click Add - button, and deselect every option but not Pattern
  • Provide a Name (e.g. AltWhiteGrey)
  • Click button OK

Now you can use this Autoformat for any arbitray range (Selecting the range) and using Format -> AutoFormat Styles

Solution 2: - Use Conditional formatting

  • Define a cell style with white background color (e.g. BGWhite)
  • Define a cell style with grey background color (e.g. BGGrey)
  • Add a conditional format using
    • Condition 1: MOD(ROW(),2)=1 - Apply Style 1 (BGWhite)
    • Condition 2: MOD(ROW(),2)=1MOD(ROW(),2)=0 - Apply Style 2 (BGGrey)
    • Cell Range: Add here the cells, where you want to apply the format

Drawback of this solution: Conditional Formats take precedence over direct formats.

Update to Solution 2 - related to comment of OP

For each cell, two conditions need to be specified. One if the row of the cell is even and one, if it is odd (see also answer of @erAck - while his solution uses functions ISVEN(),ISODD(), my solution makes use of MOD() function

image description

Hello,

Solution 1: Reusable - Define your own Auto format

  • Select range A1:D1 and set the color (e.g. white)
  • Select range A2:D2 and set the alternate color (e.g. grey)
  • Select range A3:D3 and set the color (e.g. white - step 1 needs to repeated here)
  • Select range A2:D4A1:D4 (important to have on uncoolorized line)
  • Go to menu Format -> AutoFormat Styles
  • Click Add - button, and deselect every option but not Pattern
  • Provide a Name (e.g. AltWhiteGrey)
  • Click button OK

Now you can use this Autoformat for any arbitray range (Selecting the range) and using Format -> AutoFormat Styles

Solution 2: - Use Conditional formatting

  • Define a cell style with white background color (e.g. BGWhite)
  • Define a cell style with grey background color (e.g. BGGrey)
  • Add a conditional format using
    • Condition 1: MOD(ROW(),2)=1 - Apply Style 1 (BGWhite)
    • Condition 2: MOD(ROW(),2)=0 - Apply Style 2 (BGGrey)
    • Cell Range: Add here the cells, where you want to apply the format

Drawback of this solution: Conditional Formats take precedence over direct formats.

Update to Solution 2 - related to comment of OP

For each cell, two conditions need to be specified. One if the row of the cell is even and one, if it is odd (see also answer of @erAck - while his solution uses functions ISVEN(),ISODD(), my solution makes use of MOD() function

image description

Hello,

Solution 1: Reusable - Define your own Auto format

  • Select range A1:D1 and set the color (e.g. white)
  • Select range A2:D2 and set the alternate color (e.g. grey)
  • Select range A3:D3 and set the color (e.g. white - step 1 needs to repeated here)
  • Select range A1:D4 (important to have on uncoolorized line)line)
    (Updated as per comment of OP)
  • Go to menu Format -> AutoFormat Styles
  • Click Add - button, and deselect every option but not Pattern
  • Provide a Name (e.g. AltWhiteGrey)
  • Click button OK

Now you can use this Autoformat for any arbitray range (Selecting the range) and using Format -> AutoFormat Styles

Solution 2: - Use Conditional formatting

  • Define a cell style with white background color (e.g. BGWhite)
  • Define a cell style with grey background color (e.g. BGGrey)
  • Add a conditional format using
    • Condition 1: MOD(ROW(),2)=1 - Apply Style 1 (BGWhite)
    • Condition 2: MOD(ROW(),2)=0 - Apply Style 2 (BGGrey)
    • Cell Range: Add here the cells, where you want to apply the format

Drawback of this solution: Conditional Formats take precedence over direct formats.

Update to Solution 2 - related to comment of OP

For each cell, two conditions need to be specified. One if the row of the cell is even and one, if it is odd (see also answer of @erAck - while his solution uses functions ISVEN(),ISODD(), my solution makes use of MOD() function

image description