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


The information in this article applies to:

  • Microsoft Office Excel 2003
  • Microsoft Excel 2002
  • Microsoft Excel 2000
  • Microsoft Excel 97 for Windows
  • Microsoft Excel 2004 for Mac
  • Microsoft Excel X for Mac
  • Microsoft Excel 2001 for Mac
  • Microsoft Excel 98 Macintosh Edition
This article was previously published under Q213616

SUMMARY

Microsoft Excel automatically formats new data that you type at the end of a list to match the preceding rows. You can also format a list using the AutoFormat command on the Format menu. Or, if you want, you can format a list programmatically. This article contains a sample Microsoft Visual Basic for Applications procedure to shade every other row in a selection.

MORE INFORMATION

Microsoft provides programming examples for illustration only, without warranty either expressed or implied. This includes, but is not limited to, the implied warranties of merchantability or fitness for a particular purpose. This article assumes that you are familiar with the programming language that is being demonstrated and with the tools that are used to create and to debug procedures. Microsoft support engineers can help explain the functionality of a particular procedure, but they will not modify these examples to provide added functionality or construct procedures to meet your specific requirements. To see the patterns available in Excel, click Cells on the Format menu, and then click the Patterns tab. The Pattern drop-down list box displays the available patterns. The pattern used in the following macro, referred to by its constant name, xlGray16, is the fifth one from the right in the first row.

The following macro sets the pattern in every other row of the current selection to xlGray16.

Sample Visual Basic Procedure

Sub ShadeEveryOtherRow()
    Dim Counter As Integer

    'For every row in the current selection...
    For Counter = 1 To Selection.Rows.Count
        'If the row is an odd number (within the selection)...
        If Counter Mod 2 = 1 Then
            'Set the pattern to xlGray16.
            Selection.Rows(Counter).Interior.Pattern = xlGray16
        End If
    Next

End Sub
Note This macro runs only on the rows of the selected range. If you add any new rows of data after you run the macro, you must run the macro again with all the new rows of data selected.

This process can also be done manually by using conditional formatting. For additional information about how to format every other row by using conditional formatting, click the following article number to view the article in the Microsoft Knowledge Base:

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

REFERENCES

For more information about formatting lists, click Microsoft Excel Help on the Help menu, type Format lists quickly in the Office Assistant or the Answer Wizard, and then click Search to view the topic.

For additional information about how to obtain help with Visual Basic for Applications, click the following article number to view the article in the Microsoft Knowledge Base:

226118 List of resources that are available to help you learn Visual Basic for Applications programming

Modification Type:MinorLast Reviewed:10/11/2006
Keywords:kbdtacode kbhowto kbProgramming KB213616
©2004 Microsoft Corporation. All rights reserved.