VB CODE - Highlight Intersection (Row & Column) - Help..

Sprucy

Board Regular
Joined
Oct 31, 2005
Messages
92
Hi Everyone,

Below is a great little piece of code that I came across (source unknown - sorry!)...
It works great but I've encountered a problem under certain conditions:
1- When I Save & Re-Open the file.
2- Switching from Excel to Firefox and back to Excel (Excel file still running).
This causes the VB code tp highlights the new intersection but doesn't "clear the original" intersection?

I've tried adding something like:

Cells.Select
Selection.Interior.ColorIndex = xlNone
Range("A1").Select
But obviously won't work, any ideas???



*** ACTUAL CODE *** Added to every Worksheet of my File ***

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
'Highlight Cell Intersection (Column/Row)
'Note: All other Background Colour Formats will be lost

Static LastChange
Application.ScreenUpdating = False

If LastChange = Empty Then
LastChange = ActiveCell.Address
End If

Range(LastChange).EntireColumn.Interior.ColorIndex = xlNone
Range(LastChange).EntireRow.Interior.ColorIndex = xlNone

ActiveCell.EntireColumn.Interior.ColorIndex = 43
ActiveCell.EntireRow.Interior.ColorIndex = 43

LastChange = ActiveCell.Address

Application.ScreenUpdating = True

End Sub
 

Excel Facts

Shade all formula cells
To shade all formula cells: Home, Find & Select, Formulas to select all formulas. Then apply a light fill color.
Hello!

After reviewing the code, I can see where your problem is happening when you save and re-open the file. Most likely the same thing is happening when you switch from Excel to Firefox to Excel, but I cannot duplicate.

The 'LastChange' value in the code above is unique to each worksheet the code is running on and is a volitale value, meaning it is lost when the workbook is closed and re-opened. The LastChange value is what the code uses to clear the old highlighted intersection.

One method to resolve this would be to create a non-volitatle value on a hidden sheet for each sheet you use the intersect methondon and then use a workbook_beforeSave routine to clear the intersections using these saved values.

I will leave it to you to work the details. I can assist if necessary.

Good luck!

Owen
 
Upvote 0

Forum statistics

Threads
1,213,491
Messages
6,113,963
Members
448,536
Latest member
CantExcel123

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top