Track changes on a Worksheet in Excel with VBA

Peteor

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

Some videos you may like

Excel Facts

How can you turn a range sideways?
Copy the range. Select a blank cell. Right-click, Paste Special, then choose Transpose.

Peteor

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

Peteor

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

Peteor

Board Regular
Joined
Mar 16, 2018
Messages
122
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,089,224
Messages
5,406,951
Members
403,113
Latest member
ms_excel_recal_or_die

This Week's Hot Topics

Top