I’m trying to get VBA to auto sort a column in a Table in Excel when the formula result in the cells in the same column are changed/updated. However, the auto sort code shown below is not triggering unless I manually enter a value into the cell.

I tried this code but the auto sort does not happen when the formula result changes/updates. If I manually enter a number into the cell in the column then it does auto sort.

Private Sub Worksheet_Change(ByVal Target As Range)
Dim SalesTable As ListObject
Dim SortCol As Range

Set SalesTable = ActiveSheet.ListObjects("APAC")
Set SortCol = Range("APAC[Product Value]")

If Not Intersect(Target, SortCol) Is Nothing Then
    With SalesTable.Sort
        .SortFields.Clear
        .SortFields.Add Key:=SortCol, Order:=xlDescending
        .Header = xlYes
        .Apply
    End With
End If

End Sub

Then I tried changing the event to the following but it didn’t make a difference

Private Sub Worksheet_Calculate(ByVal Target As Range)
Dim SalesTable As ListObject
Dim SortCol As Range

Set SalesTable = ActiveSheet.ListObjects("APAC")
Set SortCol = Range("APAC[Product Value]")

If Not Intersect(Target, SortCol) Is Nothing Then
    With SalesTable.Sort
        .SortFields.Clear
        .SortFields.Add Key:=SortCol, Order:=xlDescending
        .Header = xlYes
        .Apply
    End With
End If

End Sub

I also tried doing this event but it also did not work:

Private Sub Worksheet_SheetCalculate(ByVal Target As Range)
Dim SalesTable As ListObject
Dim SortCol As Range

Set SalesTable = ActiveSheet.ListObjects("APAC")
Set SortCol = Range("APAC[Product Value]")

If Not Intersect(Target, SortCol) Is Nothing Then
    With SalesTable.Sort
        .SortFields.Clear
        .SortFields.Add Key:=SortCol, Order:=xlDescending
        .Header = xlYes
        .Apply
    End With
End If

End Sub

Any suggestions?

Many thanks in advance