Microsoft KB Archive/268568

= How to use conditional formatting to shade every other row in Excel =

Article ID: 268568

Article Last Modified on 1/27/2007

-

APPLIES TO


 * Microsoft Office Excel 2003
 * Microsoft Excel 2002 Standard Edition
 * Microsoft Excel 2000 Standard Edition
 * Microsoft Excel 97 Standard Edition
 * Microsoft Excel 2004 for Mac
 * Microsoft Excel X for Mac
 * Microsoft Excel 2001 for Mac
 * Microsoft Excel 98 for Macintosh

-



This article was previously published under Q268568



SUMMARY
It is often easier to read lists of data if alternate rows are shaded. This article shows you how to use conditional formatting to shade alternate rows.

Note Conditional formatting is automatically copied to newly added rows. You can also shade alternate rows by using a Visual Basic for Applications (VBA) macro. However, if you use the VBA macro, you must run the macro again every time that new rows of data are added. For more information about how to use a macro to shade every other row in Excel, click the following article number to view the article in the Microsoft Knowledge Base:

213616 Macro to shade every other row in a selection in Excel



MORE INFORMATION
Conditional formatting is a Microsoft Excel feature that sets a cell's format according to conditions that you specify. Conditional formatting allows you to present numeric data in different colors; for example, you can shade data depending on whether the value is greater, equal to, or less than zero. You can also present list data against a background pattern of alternating shades, as in the following example.

To apply alternating shades to the rows of your worksheet, follow these steps:  Start Excel, and then open new worksheet.  Type the following data in cells A1:D4    Bob    Smith    Active   555-1111 Sue   Jones    Retired  555-2222 Alf   Wilson   Retired  555-3333 Mary  Morley   Retired  555-4444  Select the range A1:G6. On the Format menu, click Conditional Formatting. Under Condition 1, select Formula Is . In the data entry box, type =MOD(ROW,2)=1 .</li> Click the Format button. In the Format Cells dialog box, click the Patterns tab.</li> Select a light-blue color, and then click OK.</li> In the Conditional Formatting dialog box, click OK.</li></ol>

Notice that if you add more data to the list, the conditional formatting is extended to rows beyond row 6. If you want to remove the formatting from unused cells, such as in columns E through G, you can select those cells, and then delete the conditions in the Conditional Formatting dialog box.

Note If formatting is not consistently applied after data is entered, use a formula that checks for blanks first. With this approach, however, the formatting does not appear until you enter values. To do this, follow these steps:
 * 1) In Cell A1, type the following conditional formatting formula: =AND(MOD(ROW,2)=0,A1<>&quot;&quot;).
 * 2) Copy this cell to the other cells in your range to be shaded.

<div class="references_section">