Remoew row highlighting when workbook is closed

[KevHardy]

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

 

[Jacob Skaria]

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

 

[KevHardy]

Hi Jacob,
This works fine in a new workbook. However the code in my workbook
highlighted the entire row rather than a single cell.

 

[Jacob Skaria]

OK. Try th ebelow....Modified your code

Dim OldRng As Range

Private Sub Workbook_BeforeClose(Cancel As Boolean)
If Not OldRng Is Nothing Then
OldRng.EntireRow.Interior.ColorIndex = xlColorIndexNone
End If
End Sub

Private Sub Workbook_BeforePrint(Cancel As Boolean)
If Not OldRng Is Nothing Then
OldRng.EntireRow.Interior.ColorIndex = xlColorIndexNone
End If
End Sub

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
If Not OldRng Is Nothing Then
OldRng.EntireRow.Interior.ColorIndex = xlColorIndexNone
End If
End Sub


Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target
As Excel.Range)
On Error Resume Next
Target.EntireRow.Interior.ColorIndex = 6
OldRng.EntireRow.Interior.ColorIndex = xlColorIndexNone
Set OldRng = Target
End Sub

 

[KevHardy]

Sorry Jacob but this didn't work as expected. It will highlight selected row
and change when next row selected so old row is not highlighted anymore. But
if you close the workbook and open it again the last row selected is still
highlighted.

 

[KevHardy]

Is there anyway to just remove any highlighting in the entire workbook with
Work_BeforeClose? If there was this would do the trick