Custom Sort when worksheet changes

[Andy15]

I am looking for some advice.

I have some code that will sort the columns exactly as I need but currently sorts in ascending order.

I am looking to modify the code to sort by a custom order.

I recorded a macro to try and work out the code but when I place the recorded bit of code in my working section I get a Named argument not found.

Please find both pieces of code below.

Sub sort1()



Dim ColNum As Long


For ColNum = 2 To Cells(21, Columns.Count).End(xlToLeft).Column Step 3


    With Columns(ColNum)
        With Range(.Cells(21, 1), .Cells(Rows.Count, 3).End(xlUp))
            .sort Key1:=.Cells(21, 1), Order1:=xlAscending, Header:=xlYes
             
        End With
    End With


Next ColNum


End Sub

Modified code below

Sub sort1()



Dim ColNum As Long


For ColNum = 2 To Cells(21, Columns.Count).End(xlToLeft).Column Step 3


    With Columns(ColNum)
        With Range(.Cells(21, 1), .Cells(Rows.Count, 3).End(xlUp))
            .sort Key1:=.Cells(21, 1), Order1:=xlAscending, CustomOrder:= _
        "10115960,902240,11241290,10080910,11241460,10237680,11241500,10818620,10005390", Header:=xlYes
             
        End With
    End With


Next ColNum


End Sub

Also is there a way to make the code run automatically when a worksheet is updated

Thanks

 

[Andy15]

Please could somebody tell me if this is possible or not.

Thanks for any feedback