Clear fill colour of last cell on save

[trebor1956]

Hi,
I use the following code to highlight he active cell and it works fine, but when I save the workbook the last cell selected stays wit the highlighted colour. On save I would like to select say cell A1 and clear the fill colour.

Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Excel.Range)
'Update 20140923
Static xLastRng As Range
On Error Resume Next
Target.Interior.ColorIndex = 8
xLastRng.Interior.ColorIndex = xlColorIndexNone
Set xLastRng = Target
End Sub

Hope you can help.....

 

[Joe4]

OK, so it is not catching the first one when it opens up (xLastRng isn't set the first time the code is called).
We can declare the variable as a Public Variable outside of the code, and then use Workbook_Open to set the initial range.

Try this (all in the ThisWorkbook module):

Public xLastRng As Range


Private Sub Workbook_Open()
    Set xLastRng = Selection
End Sub


Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Excel.Range)
'Update 20140923
On Error Resume Next
Target.Interior.ColorIndex = 6
xLastRng.Interior.ColorIndex = xlColorIndexNone
Set xLastRng = Target
End Sub

 

[marcel49]

Thank you very much it is working now

 

[Joe4]

You are welcome.

 

[marcel49]

I wonder how I could put the cursor/cel back to color index 2 in the BeforePrint event?