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]

You can use the BeforeSave event procedure to add that coding in.
See: https://msdn.microsoft.com/en-us/vba/excel-vba/articles/workbook-beforesave-event-excel

 

[trebor1956]

Thanks,

But what code s required t go to A1 and then clear fill from A1 please?

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.....

 

[trebor1956]

Thanks, but what code do I need to go to cell A1 and clear the fill from A1 please?

 

[Joe4]

Thanks, but what code do I need to go to cell A1 and clear the fill from A1 please?

This is where the Macro Recorder is your best friend! It is a great tool to use in helping to create your own VBA code.
Turn on the Macro Recorder, and record yourself performing those steps manually, and you will have the VBA code that you need.
Try it out for yourself!
Post back here if you run into issues.

 

[trebor1956]

This is where the Macro Recorder is your best friend! It is a great tool to use in helping to create your own VBA code.
Turn on the Macro Recorder, and record yourself performing those steps manually, and you will have the VBA code that you need.
Try it out for yourself!
Post back here if you run into issues.

Thanks, that worked fine.

 

[Joe4]

Thanks, that worked fine.

Excellent! Glad to hear it!


The Macro Recorder is a handy tool that even us "pros" use (you don't think we remember all this stuff, do you)?

 

[marcel49]

When I open my workbook with the mentioned macro a cell is yellow when I go to another cel the first cel is stil yellow. What can I do to prvent that?

 

[Joe4]

When I open my workbook with the mentioned macro a cell is yellow when I go to another cel the first cel is stil yellow. What can I do to prvent that?

Please post the code that you are using, and explain exactly what you want to happen.

 

[marcel49]

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 = 6
xLastRng.Interior.ColorIndex = xlColorIndexNone
Set xLastRng = Target
End Sub

this is the code, When I open my workbook a random cel where the cusor is at that moment is yellow. When I move to another cel that cel becomes yellow but the first one is still yellow and that should not happen that cell should be return to its default state.