Row and column background color using VBA in Microsoft Excel

The macros below can be used to set the background color of e.g. every other row or column in any worksheet range:

Sub ShadeAlternateRows(rngTarget As Range, intColor As Integer, lngStep As Long)
' adds a background color = intColor to every lngStep rows in rngTarget
' example: ShadeAlternateRows Range("A1:D50"), 27, 2 
' colors every 2 rows light yellow
Dim r As Long
    If rngTarget Is Nothing Then Exit Sub
    With rngTarget
        .Interior.ColorIndex = xlColorIndexNone 
        ' remove any previous shading
        For r = lngStep To .Rows.Count Step lngStep
            .Rows(r).Interior.ColorIndex = intColor
        Next r
    End With
End Sub

Sub ShadeAlternateColumns(rngTarget As Range, _
    intColor As Integer, lngStep As Long)
' adds a background color = intColor to every lngStep column in rngTarget
' example: ShadeAlternateColumns Range("A1:J20"), 27, 2 
' colors every 2 columns light  yellow
Dim c As Long
    If rngTarget Is Nothing Then Exit Sub
    With rngTarget
        .Interior.ColorIndex = xlColorIndexNone 
        ' remove any previous shading
        For c = lngStep To .Columns.Count Step lngStep
            .Columns(c).Interior.ColorIndex = intColor
        Next c
    End With
End Sub

Users are saying about us...

  1. “Thank you for sharing. But it would be nice if there was link that points to HOWTO use this macro.
    It’s what I need, but I have no idea how to apply it to excel.
    Cheers”

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>

Terms and Conditions of use

The applications/code on this site are distributed as is and without warranties or liability. In no event shall the owner of the copyrights, or the authors of the applications/code be liable for any loss of profit, any problems or any damage resulting from the use or evaluation of the applications/code.

Visit Us On TwitterVisit Us On FacebookVisit Us On Google PlusVisit Us On Youtube