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:
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.
- Click the Format button. In the Format Cells dialog box, click the Patterns tab.
- Select a light-blue color, and then click OK.
- In the Conditional Formatting dialog box, click OK.
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:
- In Cell A1, type the following conditional formatting formula: =AND(MOD(ROW(),2)=0,A1<>"").
- Copy this cell to the other cells in your range to be shaded.
Excel 2007 & Excel 2010 (see also KB919194)
1. Complete Steps 1, 2 & 3 above.
2. Home ribbon > Styles group > Conditional Formatting > New Rule
3. Under 'Select a Rule Type' choose: Use a formula to determine which cells to format
4. Under 'Edit the Rule Description' type =MOD(ROW(),2)=1 in the 'Format values where this formula is true'
5. Click the Format button and select your choices > OK
Excel 2007 & Excel 2010 Note:
If you only want the formatting, you can also change your data to a table and use built in formatting.
1. Complete steps 1 & 2 above.
2. Click your cursor with your range of data or select the data
3. Home ribbon > Styles group > Format as Table > choose the format you'd like applied to the selection
* This process will change your data to a table with filters. If you do not like this, you may "Convert to Range" in the Table Tools > Design > Tools to return the data back to original state and use the conditional formatting option above.
For more information about the MOD worksheet function, click Microsoft Excel Help on the Help menu, type mod in the Office Assistant or the Answer Wizard, and then click Search to view the topics returned.
Article ID: 268568 - Last Review: Jan 7, 2017 - Revision: 1