SelectionChange event not firing

If you change one cell, the PreviousValues will not be an array, and your Change event code won't work. Equally, if you did change more than one value, it will be a 2 dimensional array, not 1D, so your code will fail. You won't know that either way though because you stuck On Error Resume Next at the top of it, so nothing will appear to be wrong.

Personally, my preference would be to use just the Change event, store the Target.Value in your variable, disable events, then use Application.Undo to remove the changes and then test the old value(s) as appropriate. You can then reinstate the changes and reset events.

Hi all,

Thanks for your quick replies. Scoping shouldn't be the issue (as far as I can tell now), as I have declared the PreviousValues outside of the events, so it can be used by both Change and SelectionChange events. I had it working yesterday and it could detect all changes in an array and make note of that in the AuditTrail worksheet, which is a functionality I'd like to preserve as I am often finding myself adjusting multiple cells in the same time (proprietary information, so unable to share). Hoping you can have a look and help me out, thanks again!

Best regards,
Don

Respectfully, there is no way the code you posted ever worked. Target(c).Value and PreviousValues(c) make no sense at all if c is a Range object, in addition to the errors I mentioned previously.

You could do something like this:

Private Sub Worksheet_Change(ByVal Target As Range)
    
    Dim LR     As Long
    Dim x As Long, y As Long
    Dim a As Range
    Dim sel As Range
    Dim PreviousValues As Variant
    
'    On Error Resume Next
    Application.EnableEvents = False
    Set sel = Selection
    Set a = Target
        PreviousValues = a.Value
        Application.Undo
        If a.Count > 1 Then
        For x = 1 To a.Rows.Count
            For y = 1 To a.Columns.Count
                If a.Cells(x, y) <> PreviousValues(x, y) Then
            With Sheets("AuditTrail")
                     LR = .Cells(Rows.Count, "A").End(xlUp).Row + 1
                    .Cells(LR, "A").Value = Now
                    .Cells(LR, "B").Value = Environ("UserName")
                    .Cells(LR, "C").Value = ActiveSheet.Name & "!" & a.Cells(x, y).Address
                    If PreviousValues(x, y) = "" Then
                        .Cells(LR, "D").Value = "BLANK!"
                    Else
                        .Cells(LR, "D").Value = PreviousValues(x, y)
                    End If
                    If a.Cells(x, y).Value = "" Then
                        .Cells(LR, "E").Value = "BLANK!"
                    Else
                        .Cells(LR, "E").Value = a.Cells(x, y).Value
                    End If
            End With
        End If
        Next y
    Next x
    Else
                 If a.Value <> PreviousValues Then
            With Sheets("AuditTrail")
                     LR = .Cells(Rows.Count, "A").End(xlUp).Row + 1
                    .Cells(LR, "A").Value = Now
                    .Cells(LR, "B").Value = Environ("UserName")
                    .Cells(LR, "C").Value = ActiveSheet.Name & "!" & a.Address
                    If PreviousValues = "" Then
                        .Cells(LR, "D").Value = "BLANK!"
                    Else
                        .Cells(LR, "D").Value = PreviousValues
                    End If
                    If a.Value = "" Then
                        .Cells(LR, "E").Value = "BLANK!"
                    Else
                        .Cells(LR, "E").Value = a.Value
                    End If
            End With
        End If
       End If
    Target.Value = PreviousValues
    sel.Select
Ending:
    On Error GoTo 0
    Application.EnableEvents = True
    
End Sub