Greetings,
I found this code on another thread posted last summer
The code does *exactly* what I wanted it to do: highlight the active row yellow without overwriting any conditional formatting.
The only problem is: when you save/close the workbook, and re-open, the last row that was selected before closing remains highlighted and it doesn't go away unless you manually select the row and change the fill. This issue was raised by the original poster but no-one ever replied. I hope I'm not breaking any forum rules by reposting.
Below is the code and attached is a workbook with an example of how the row stays yellow.
Active Row Highlight Problem Example.xls
Thank you kindly in advance for your help!!
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
' HIGHLIGHT THE ENTIRE ROW YOU HAVE HIGHLIGHTED (ONLY USE ON SHEETS WITH COLOR/FORMATTING COMPLETE)
Const cnNUMCOLS As Long = 100
Const cnHIGHLIGHTCOLOR As Long = 36 'default lt. yellow
Static rOld As Range
Static nColorIndices(1 To cnNUMCOLS) As Long
Static nBoldIndices(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
With .Item(i)
.Interior.ColorIndex = nColorIndices(i)
.Font.Bold = nBoldIndices(i)
End With
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
If .Item(i).Font.Bold Then
nBoldIndices(i) = True
Else
nBoldIndices(i) = False
End If
Next i
.Interior.ColorIndex = cnHIGHLIGHTCOLOR
.Font.Bold = True
End With
' Cells(ActiveCell.Row, "C").Interior.ColorIndex = nColorIndices(3)
End Sub
Bookmarks