New Excel Forum

This forum has been moved to TeachExcel.com

Ask all future questions in the New Excel Forum.

ExcelKey

Shade Every Other Row in a Worksheet

Free Excel Macros

Shade Every Other Row in a Worksheet

Postby Sisyphus » Wed Feb 29, 2012 9:47 am

This little gadget will make your worksheet much easier to read:
Code: Select all
Public Sub ShadeRows()

    Const Shade As Long = 15            ' PatternColorIndex
    Const FirstShadedRow As Long = 5
    Const Grouping As Long = 3          ' First of group is shaded
    Const ShadeWidth As Long = 7        ' 0 = entire row
    Const TestColumn As String = "A"    ' to find the last used row
   
    Dim R As Long
    Dim Rng As Range
   
    For R = Cells(Rows.Count, TestColumn).End(xlUp) _
                 .Row To FirstShadedRow Step -1
        If (R - FirstShadedRow) Mod 3 = 0 Then
            If Cells(R, 1).Interior.ColorIndex = Shade Then
                Exit For
            Else
                If ShadeWidth Then
                    Set Rng = Range(Cells(R, 1), Cells(R, ShadeWidth))
                Else
                    Set Rng = Rows(R).EntireRow
                End If
                Rng.Interior.ColorIndex = Shade
            End If
        End If
    Next R
End Sub

Install the macro in a normal code module ('Module1' by default).
Set the 5 constants to the values you want. You can set the shading color, the first row to shade, each how many rows you want one row shaded, the width of the shading (number of columns or 0 for entire width) and the column that defines the end of the sheet.

The macro will start shading from the bottom of the sheet and stop when a cell is encountered in column A that is already shaded. This is so that you can run the program repeatedly on the same sheet, for example, whenever you have additions to it.

The macro can be called manually (Tools / Macro / Macros - select 'ShadeRows' - press 'Run') or by other code. In the latter case make sure that sheet to be treated is the ActiveSheet.
Have a great day! :D

Sisyphus
I do this for "honour and country" - much less of the latter, actually.
If I helped you, award points, plenty of them.
If I bored you, deduct points for being too long-winded. (I know, :lol)
Sisyphus
Former Moderator
 
Posts: 4454
Joined: Dec 7, 2011
Location: Shanghai
Reputation: 203
Excel Version: 2010

Return to Excel Macros

Who is online

Users browsing this forum: No registered users and 28 guests