I am trying to find the absolute difference between the value in Cell C3 in Sheet2 and the values in Col C in Sheet1. After which, the differences are sorted in Descending order. The differences calculated will be inputted into column D. I have put everything in the change event which is triggered whenever column C is changed. This is the code thus far, there are no errors being thrown up but the absolute differences are also not showing in column D in Sheet1. Can anyone help me to see what's wrong here? Any help is greatly appreciated!!


Private Sub Worksheet_Change(ByVal Target As Range)

Dim i As Long
Dim test As Variant
Dim calc As Variant

If Not Intersect(Target, Range("C:C")) Is Nothing Then
    test = Sheet2.Cells(3, 3).Value
    For i = 1 To Sheet1.Cells(Rows.Count, "A").End(xlUp).Row
        calc = Sheet1.Cells(i + 1, "C").Value
        Sheet1.Range("D" & i).Value = Abs(test - calc)
        Application.EnableEvents = False
        Range("A:D").Sort Key1:=Range("D2"), _
              Order1:=xlDescending, Header:=xlYes, _
              OrderCustom:=1, MatchCase:=False, _
              Orientation:=xlTopToBottom
        Application.EnableEvents = True
    Next i
End If

End Sub