Hi

Try the below in a fresh workbook..Paste the code in 'This WorkBook'
....Incase the target cells are already colored the below code will restore
the colors on selection change. and should also restore the colorindex on
save,close,print etc...Try and feedback....


Dim arrIndex As Variant
Dim OldCell As Range

Private Sub Workbook_BeforeClose(Cancel As Boolean)
If Not OldCell Is Nothing Then ReSetColorIndex OldCell
End Sub

Private Sub Workbook_BeforePrint(Cancel As Boolean)
If Not OldCell Is Nothing Then ReSetColorIndex OldCell
End Sub

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
If Not OldCell Is Nothing Then ReSetColorIndex OldCell
End Sub

Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, _
ByVal Target As Excel.Range)

If Not OldCell Is Nothing Then ReSetColorIndex OldCell
SetColorIndex Target
Set OldCell = Target
Target.Interior.ColorIndex = 4
End Sub

Sub SetColorIndex(varRange As Range)
Dim lngCount As Long, cell As Range
ReDim arrIndex(varRange.Count)
For Each cell In varRange
lngCount = lngCount + 1
arrIndex(lngCount) = cell.Interior.ColorIndex
Next
End Sub

Sub ReSetColorIndex(varRange As Range)
Dim lngCount As Long, cell As Range
For Each cell In varRange
lngCount = lngCount + 1
cell.Interior.ColorIndex = arrIndex(lngCount)
Next
End Sub

--
Jacob


"KevHardy" wrote:

Hi,
Found a bit of code on the web (below) to highlight the active row but need
something that will remove the highlight (not the data) when the workbook is
closed so that the next time you open it nothing is highlighted.
At present when you re-open it a row is still highlighted and then when you
select a cell you end up with two rows highlighted!

Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target
As Excel.Range)
Static OldRng As Range

On Error Resume Next
Target.EntireRow.Interior.ColorIndex = 6
OldRng.EntireRow.Interior.ColorIndex = xlColorIndexNone
Set OldRng = Target

End Sub