Sort automatically

[MAX]

Hello,

I created a table with a range of "A1:I6".

In row 1 starting from A1 I have these titles:
Team Play Win Draw Lose GF GA GD Points

In column A starting from A2 I have These names:
Roma
Milan
Lazio
Bari
Siena

The table is updated from such fixtures in the same sheet and all I want is
that when I post the results in these fixtures the table sort automatically
with these criterias.
1. The more points( Column I)
then 2. The more GD (Column H)
then 3. The more GF (Column F)

I want to sort this range "A2:I6".
I also attached the code (below), but when I post a result and
these criterias change, a window is coming up with Compile error and
Syntax error. Why?

This is the code that I am using:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim isect As Range
Set isect = Application.Intersect(Target, Range("F2:I6"))
If Not isect Is Nothing Then
With ActiveSheet
With .Sort.SortFields
.Clear
.Add Key:=Range("I2"), _
SortOn:=xlSortOnValues, Order:=xlDescending,
DataOption:=xlSortNormal
.Add Key:=Range("H2"), _
SortOn:=xlSortOnValues, Order:=xlDescending,
DataOption:=xlSortNormal
.Add Key:=Range("F2"), _
SortOn:=xlSortOnValues, Order:=xlDescending,
DataOption:=xlSortNormal
End With
With .Sort
.SetRange Range("A1:I6")
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
End With
End If
End Sub


Thanks.

 

[Bernd P]

Hello,

I suggest to use my function GSort:
http://www.sulprobil.com/html/sort_vba.html

Regards,
Bernd

 

[Shane Devenshire]

Hi,

but what criteria are changing? The code works on you original sample but
you aren't telling us enough.