Microsoft Excel - Automatic resort of columns when numbers change

Asked By max Parris on 01-Feb-12 10:48 AM
Hi guys. I have an excel ss showing some data for stocks I have. I would like to automatic resort the colunms when
lets say the number in column J change so that the number is column J are high to low all the time.
Thanks for any help on this.
Max
wally eye replied to max Parris on 01-Feb-12 12:47 PM
I just posted an autosort solution here:

http://www.eggheadcafe.com/community/excel/66/10411654/auto-sort.aspx

You should be able to tweak it to fit your sheet.
kalpana aparnathi replied to max Parris on 01-Feb-12 01:57 PM
hi,

Do following:

1.Right click a sheet where you want to sort and select View|Code

2. In the right hand side paste the following code then close the visual Basic Editor by selecting File|Close & Return to Excel (or Alt+Q).
Code:
Private Sub Worksheet_Change(ByVal Target As Range)
Dim Rng As Range
On Error GoTo ErrHand

'Only run macro if one cell was changed not multiple cells
If Target.Cells.Count > 1 Then GoTo ErrHand

'If change was in col H, then sort col H descending
If Target.Column = 8 Then
Set Rng = Range("A3:H" & Range("H65536").End(xlUp).Row)
Rng.Sort Key1:=Range("H4"), Order1:=xlDescending, Header:= _
        xlGuess, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
        DataOption1:=xlSortNormal
End If

ErrHand:
Exit Sub

End Sub