Track changes on a Worksheet in Excel with VBA

Peteor

Board Regular
Joined
Mar 16, 2018
Messages
114
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

Board Regular
Joined
Mar 16, 2018
Messages
114
***Skip would just exit the Sub. I forgot to show that.***
EG...
Skip:
End Sub
 

Peteor

Board Regular
Joined
Mar 16, 2018
Messages
114
Does this mean no-one has any input here? I am kinda stumped by this one....
 

Peteor

Board Regular
Joined
Mar 16, 2018
Messages
114
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?
 
Last edited:

Forum statistics

Threads
1,078,364
Messages
5,339,760
Members
399,321
Latest member
ladeko

Some videos you may like

This Week's Hot Topics

Top