Highlighting Active rows without losing background color saving issues

[TinyDancer528]

I am using the following code to highlight the active row in my spreadsheet while not losing my previously set background colors. The issue is when I save the document the previous active row stays yellow. I tried to use the "beforesave" in the workbook's code, but I don't think I did it right. Any suggestions?

Private Sub Worksheet_SelectionChange(ByVal Target As 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
Application.ScreenUpdating = False
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
If nColorIndices(i) = xlNone Then
.Item(i).Interior.ColorIndex = xlNone
Else
.Item(i).Interior.Color = nColorIndices(i)
End If
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.Color
If .Item(i).Interior.ColorIndex = xlNone Then
nColorIndices(i) = xlNone
Else
nColorIndices(i) = .Item(i).Interior.Color
End If
Next i
.Interior.ColorIndex = cnHIGHLIGHTCOLOR
End With
Application.ScreenUpdating = True
End Sub

 

[MrMeinHerr]

You might want to investigate a simpler solution. Use conditional formatting to highlight the active row.

The formula to try might be something like this:

=(CELL("row")=CELL("row",A1))

... and be sure to apply it to the entire worksheet, i.e. $1:$1048576 if you're using Excel later than 2003.

Then include a much simpler Worksheet_SelectionChange subroutine like this:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    Target.Calculate
End Sub

It's another option that might provide you with similar functionality, while avoiding the problem you are describing. I hope this helps!

 

[TinyDancer528]

Thank you for responding.

This conditional formatting did not work. My active row no longer highlights.

Maybe I did something wrong?

 

[Brian from Maui]

Highlight your range you want and using CF, enter

=CELL("Row")=ROW()

then add this code to the worksheet

Private Sub Worksheet_SelectionChange(ByVal Target As Excel.Range)
Application.ScreenUpdating = True
End Sub

 

[TinyDancer528]

Highlight your range you want and using CF, enter

=CELL("Row")=ROW()

then add this code to the worksheet

Private Sub Worksheet_SelectionChange(ByVal Target As Excel.Range)
Application.ScreenUpdating = True
End Sub

This was perfect!!! Thank you!

 

[scoobyblue]

I'd like to do something similar, can anyone help explain what 'CF' is?

Thanks...

Highlight your range you want and using CF, enter

=CELL("Row")=ROW()

then add this code to the worksheet

Private Sub Worksheet_SelectionChange(ByVal Target As Excel.Range)
Application.ScreenUpdating = True
End Sub

 

[MrMeinHerr]

I'd like to do something similar, can anyone help explain what 'CF' is?

Thanks...

"CF" is an acronym referring to "Conditional Formatting."

 

[scoobyblue]

of course, many thanks for your reply.

"CF" is an acronym referring to "Conditional Formatting."