ERLANDSEN DATA CONSULTING Excel & VBA Tips   Informasjon på norsk / Information in Norwegian

These pages are no longer updated and are only available for archive purposes.

Click here to visit the pages with updated information.

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

 

Erlandsen Data Consulting     http://www.erlandsendata.no/   
Excel & VBA Tips   Copyright ©1999-2017    Ole P. Erlandsen   All rights reserved
E-mail Contact Address