This site will look better if you upgrade to a browser that supports web standards.

McGimpsey & Associates 

Home

General XL Tips

XL Formulae

Using VBA with XL

XL VBA Functions

XL VBA Macros

Mac XL

This site will look better if you upgrade to a browser that supports web standards.

Highlight a row without losing background colors

Sometimes, in order to more easily see where one is on a worksheet, it's convenient to highlight the entire active row. A common way to do this is to use a Worksheet_SelectionChange() event macro, something like:

    Private Sub Worksheet_SelectionChange(ByVal Target As Excel.Range)
        Cells.Interior.ColorIndex = xlColorIndexNone
        ActiveCell.EntireRow.Interior.ColorIndex = 36
    End Sub

While this is fine for the majority of occasions when one hasn't set the background color, if the background color is set, the above macro will remove the color. The following macro stores the cell background colors in an array and writes them back to the cells when another cell is selected.

    Private Sub Worksheet_SelectionChange(ByVal Target As Excel.Range)
        Const cnNUMCOLS As Long = 256
        Const cnHIGHLIGHTCOLOR As Long = 36  'default lt. yellow
        Static rOld As Range
        Static nColorIndices(1 To cnNUMCOLS) As Long
        Dim i As Long
        If Not rOld Is Nothing Then 'Restore color indices
            With rOld.Cells
                If .Row = ActiveCell.Row Then Exit Sub 'same row, don't restore
                For i = 1 To cnNUMCOLS
                    .Item(i).Interior.ColorIndex = nColorIndices(i)
                   Next i
            End With
        End If
        Set rOld = Cells(ActiveCell.Row, 1).Resize(1, cnNUMCOLS)
        With rOld
            For i = 1 To cnNUMCOLS
                nColorIndices(i) = .Item(i).Interior.ColorIndex
            Next i
            .Interior.ColorIndex = cnHIGHLIGHTCOLOR
        End With
    End Sub

The downside here is that it becomes more difficult to set the background color. The macro is also fairly sluggish when highlighting and restoring all 256 columns. Changing NUMCOLS to a smaller number significantly speeds things up.

This page last updated Sunday, 4 January 2004

© Copyright 2001 - 2004 McGimpsey and Associates. Except where noted, all code on this site may be distributed under the Gnu GPL. Acknowledgement is appreciated.

Quick Links

For a different approach, see Chip Pearson's excellent Rowliner add-in