Sort in a worksheet change event

[Xenowyn]

So I have the following code:

Private Sub Worksheet_Change(ByVal Target As Range)

Application.EnableEvents = False
If Target.Column = 1 Then
    Columns(1).Sort key1:=Range("A3"), order1:=xlAscending, Header:=xlYes
End If
Application.EnableEvents = True

End Sub

I want to sort the values in column A in ascending order (the header consists of rows 1 and 2 merged) only if a cell in column A is changed. But when I change a cell in column A with this code in place, nothing happens. Any help?

 

[mikerickson]

There is a work-around to keep the changed cell Active after the sort. But it depends on the values in column A being unique.
Are your col A values unique?

 

[Xenowyn]

Yes they are unique.

 

[mikerickson]

In that case, try this

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim tValue As Variant, rOffset As Long, cOffset As Long
    
    With Target
        If .Column = 1 Then
            tValue = .Cells(1, 1).Value
            rOffset = ActiveCell.Row - .Row
            cOffset = ActiveCell.Column - .Column
            
            With Columns(1)
                With Range(.Cells(3, 1), .Cells(Rows.Count, 1).End(xlUp)).EntireRow
                    .Sort key1:=.Cells(1, 1), order1:=xlAscending, Header:=xlNo
                End With
                With .Find(tValue, after:=.Cells(1, 1), LookIn:=xlValues, lookat:=xlWhole)
                    Application.Goto .Cells
                    If .Row = 3 And rOffset = -1 Then rOffset = 0
                    .Offset(rOffset, cOffset).Select
                End With
            End With
        End If
    End With
    Application.EnableEvents = True
End Sub

 

[Hercules1946]

No worries - the important thing is its resolved.