[RESOLVED] Highlight active cell in Excel

[krtxmrtz]

I use this code to highlight the sheet's active cell:

Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Excel.Range)
    Static OldRange As Range
    On Error Resume Next
    Target.Interior.ColorIndex = 6 ' yellow - change as needed
    OldRange.Interior.ColorIndex = xlColorIndexNone
    Set OldRange = Target
End Sub

that I found here.

All the cells in my sheet are white (default) except for a few in various other colours. Anyone knows how to modify the code so that the old colour is restablished after the cell is no longer actove?

[RobDog888]

Save the range to a module level range variable object. Then update it in the event. Set your range variable interior color back to default, set the new range to the color 6, then update the range variable object for the next event so you will know which range to reset.

[krtxmrtz]

Save the range to a module level range variable object. Then update it in the event. Set your range variable interior color back to default, set the new range to the color 6, then update the range variable object for the next event so you will know which range to reset.

It works as you say, indeed. Thank you.

'In a module
Global oldrng As Range
Global oldcol As Long
'In ThisWorkbook
Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Excel.Range)
    On Error Resume Next
    oldrng.Interior.ColorIndex = oldcol
    oldcol = Target.Interior.ColorIndex
    Target.Interior.ColorIndex = 6 ' yellow or whatever
    Set oldrng = Target
End Sub