Row and column background color

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

You can use select a worksheet range and select Format, Conditonal Formatting and fill in the dialog like the example below to apply alternate background color to every other row:

You can use select a worksheet range and select Format, Conditonal Formatting and fill in the dialog like the example below to apply alternate background color to every other column:

Document last updated 2002-07-17 12:51:27 Printerfriendly version

User comments:

Wisnu Kusumoaji from Jakarta, Indonesia wrote (2006-08-10 13:45:09 CET):

Conditional Formatting
Thank You

I have modified your macro from the "Conditional formatting of cells"
it really works fast..
Thank you,never thought of using the styles.

Regards

Kusumoaji@telkom.net

Ole P. from Norway wrote (2006-08-10 12:45:25 CET):

Re: Conditional Formatting
Excel currently only supports 3 conditional formats.
You will have to write your own solution if you need more than 3 conditional formats, or use third-party solutions like you have tried.
Excel 2007 does not have this limit on conditional formats.

Wisnu Naumowicz Kusumoaji from Jakarta, Indonesia wrote (2006-08-10 09:47:24 CET):

Conditional Formatting
Sorry to bother you, but can you give me a hint about how to create the conditional formatting for more then 3 condition (ie using 5-6 colors with different condition).

i already try the program "CFPlus" version 1.02, but it works SO SLOW,
i used it in my spreadsheet combine with my vba program, if i dont use the "CFPlus" program, my calculation only takes 2 - 5 minutes. But whenever i use the "CFPlus" the time elapse till 3 - 5 HOURS!!

Really annoying.
Thank you very much..

Regards

Kusumoaji@telkom.net