Highlight Active Row macro error

hi,
I have a macro to highlight the active row of the highlighted cell, but there is one problem. When I save and reopen the workbook, the last row that was highlighted when saved remains permanently highlighted. So if I do this 10 times, I will have 10 rows permanently highlighted.
the code is as under:


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

So for example if I last click on cell E25, entire row 25 gets highlighted, and when I save and reopen, row 25 remains highlighted no matter what I do.. It cannot be altered!!!

I need a way to ensure that when I save and reopen, the active cell does not remain permanently highlighted. Thanks in advance!!

how about putting this in the 'ThisWorkbook' object

Please Login or Register  to view this content.

this will make the row a blank colour when you press save - if that doesn't work exactly as you need then you might want to try it with the Workbook_Close method

John

hey John,
Thanks for your reply...
When I put it under This Workbook, it completely stops working.. and I don't see how the Workbook_Close would help..

Is there anything I can edit or add to the code to ensure that when I reopen the workbook, it doesn't 'save' or 'remember' the last row highlighted and make it permanent??

Thanks in advance! really appreciate it!!

Oh actually it works perfectly!!!!!

You rock man!! thank you soo much!!!!!

No now it makes all the rows white when saving.. I need it to have no effect when saving

what do you mean by it makes "all the rows" white. It should only make the current row white (which I am trying to fix now)

If you don't mind row 1 being excluded from the roaming routine then this code should work

Please Login or Register  to view this content.

and then in the Before_Save code just get it to go to cell A1 which will put the previous row back to the right colour

Please Login or Register  to view this content.

hope that makes sense. If you don't want it to go to cell A1 when the user saves then put the A1 code in the Before_close method

Yes works perfectly!!

Thank you soo much!