Highlight a row without losing background colors

[Naceur]

Hi every one,

I have found this VBA code in McGimpsey & Associates side. It work very
well but the only problem is that went we close the workbook and we open
it later the last selected row before closing during the last time
remained in highlight color.

My idea is to restore the color index of that row before closing the
file.


How can I do this??


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


Thank you and Best Regards

 

[Tom Ogilvy]

You would have to use the BeforeSave event in the Thisworkbook module.

You would need to make the variables that store the old color and range in a
general module as public variable (remove the declarations from the
Selectionchange event)

Public rOld As Range
Public nColorIndices(1 To cnNUMCOLS) As Long

This would make them visible both events.

then you could reproduce the code to reset the color in the BeforeSave
event.

See Chip Pearson's page on events if you are not familiar with them
(although you are using one)

http://www.cpearson.com/excel/events.htm

 

[David McRitchie]

I think Chip Pearson's row liner addin is a better solution, since you
are less likely to change the cell border colors. Of course you would
be sent there from the other page of his.
RowLiner Add-In
http://www.cpearson.com/excel/RowLiner.htm

I think by storing the original colors you still might leave yourself open
to restoring over a color you deliberately changed.
---
HTH,
David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001]
My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm
Search Page: http://www.mvps.org/dmcritchie/excel/search.htm

You would have to use the BeforeSave event in the Thisworkbook module.

You would need to make the variables that store the old color and range in a
general module as public variable (remove the declarations from the
Selectionchange event)

Public rOld As Range
Public nColorIndices(1 To cnNUMCOLS) As Long

This would make them visible both events.

then you could reproduce the code to reset the color in the BeforeSave
event.

See Chip Pearson's page on events if you are not familiar with them
(although you are using one)

http://www.cpearson.com/excel/events.htm