Track changes on a Worksheet in Excel with VBA

[Peteor]

I am currently using the following:

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("O:T")) Is Nothing Or Range("W2") = vbNullString Then GoTo Skip
If Range("W2") <= Range("W1") Then
If Not Intersect(Target, Range("A:U")) Is Nothing Then
Call Automatic_Highlights(Target)
End If
End If

End Sub


Which Calls the following when a change is made:

Sub Automatic_Highlights(Rng As Range)


Rng.Interior.ColorIndex = 26
Intersect(Rng.EntireRow, Range("U:U")).Interior.ColorIndex = 26


End Sub



It works, and is currently in use. However..... There are annoying nuances. EG.... When a cell is copied, and a user presses enter, it calls Automatic_Highlights. EG.... When you actually enter the cell as if to type, but do not change the value, when you click off, it calls Automatic_Highlights.

I think a solution would be to somehow track the cell value before and after the event, and highlight if they are not the same. Honestly, I have written MANY macros, but am unsure how this one is operating regarding the intersect method. Would anyone have any suggestions?

 

[Peteor]

***Skip would just exit the Sub. I forgot to show that.***
EG...
Skip:
End Sub

 

[Peteor]

Does this mean no-one has any input here? I am kinda stumped by this one....

 

[Peteor]

I am working a solution but am having an issue with the Target.Value method. See the below:

Dim oldValue As Variant
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
oldValue = Target.Value
End Sub
Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("O:T")) Is Nothing Or Range("W2") = vbNullString Then GoTo Skip
If Range("W2") <= Range("W1") Then
If Not Intersect(Target, Range("A:U")) Is Nothing Then
If Target.Value <> oldValue Then
Call Automatic_Highlights(Target)
Else
End If
End If
End If


Skip:
If Not Intersect(Target, Range("O:T")) Is Nothing Then
Call Date_Highlights(Target)
End If




End Sub

This works for single cell changes, but has a mismatch error when copying/pasting a multiple cell selection. I see the .Value method is only for single cells. How would I accomplish the same, but account for a multiple cell selection? Is my only option to consider using dynamic arrays?