This site will look better if you upgrade to a browser that supports web standards.
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
© Copyright 2001 - 2004 McGimpsey and Associates. Except where noted, all code on this site may be distributed under the Gnu GPL. Acknowledgement is appreciated.
For a different approach, see Chip Pearson's excellent Rowliner add-in