Active Cell Selector

[kevin21]

I found some code on an old Excel site on how to highlight the active cell. When this is running it does not allow you to copy and paste because you are updating the cell when you go to it. Is there a way to know if data is in memory for pasting so I can disable the code or is there a better way to highlight the active cell. I am posting the code below. Thanks

Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Excel.Range)

Static OldRange As Range
Static OldColor As Integer
Static NewColor As Integer
On Error Resume Next
NewColor = Target.Interior.ColorIndex
Target.Interior.ColorIndex = 6 ' yellow - change as needed
OldRange.Interior.ColorIndex = OldColor
Set OldRange = Target
OldColor = NewColor
End Sub

 

[VoG]

There is a better way. Select the entire range in the sheet, apply the following Conditional Formatting formula

=AND(ROW()=CELL("row"),COLUMN()=CELL("col"))

and select a suitable fill colour.


Right click the sheet tab, select View Code and paste in

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Application.ScreenUpdating = True
End Sub

 

[kevin21]

Thanks, but it seems my screen flashes several times before the color changes. Is there something else I need to look at?

 

[VoG]

Did you remove your other code first?