Worksheet_change: i do not want it to activate when value in cell does not change

Question

Currently, I am using worksheet_change to automatically change cell colours when a user makes any changes to a cell's value. However, my else clause runs when a user enters and leaves a cell without changing the cell's value (User probably double clicked a cell, then clicked on a different cell instead of pressing escape.) . How can I amend my code so that the Else clause only runs if the user has made changes to the cell's value?

Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Target, Range("A1:Z200")) Is Nothing Or Target.Cells.Count > 1 Then

Exit Sub

Else

Range(Target.Address).Interior.ColorIndex = 43

End If

You could get the old value before the change and compare it with the new value -- see [here](https://stackoverflow.com/questions/4668410/how-do-i-get-the-old-value-of-a-changed-cell-in-excel-vba). — Zev Spitz, Apr 11 '18 at 04:28
Possible duplicate of [How do I get the old value of a changed cell in Excel VBA?](https://stackoverflow.com/questions/4668410/how-do-i-get-the-old-value-of-a-changed-cell-in-excel-vba) — Zev Spitz, Apr 11 '18 at 04:28

score 0 · Answer 1 · answered Apr 11 '18 at 03:18

You either have to:

Educate users to push Escape rather than Enter if they have gone into Edit mode but have changed their mind; or
Make a copy of the range, check the value against the copy and if they've made changes, update the copy with the new value and change the original cell color.

score 0 · Answer 2 · answered Apr 11 '18 at 04:35

Please give this a try...

Dim oVal
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.CountLarge > 1 Then Exit Sub
If Not Intersect(Target, Range("A1:Z200")) Is Nothing Then
    If Target <> oVal Then
        Range(Target.Address).Interior.ColorIndex = 43
        oVal = Target
    End If
End If
End Sub

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target.CountLarge > 1 Then Exit Sub
If Not Intersect(Target, Range("A1:Z200")) Is Nothing Then
    oVal = Target.Value
End If
End Sub

Worksheet_change: i do not want it to activate when value in cell does not change

2 Answers2