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


One thought on “Row and column background color using VBA in Microsoft Excel

  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>