Excel, Auto sort a colomb

DJMK4

DJMK4

DJMK4

Soldato
Joined
1 Dec 2004
Posts
23,047
Location
S.Wales
I have a colomb in excel, ranging from G4- G23 which i have sorted in ascending order, but everytime i change some data, it does not automatically update the sort. Is there a way to do this??

Iv search but the only way i can find it is by doing Macro's..
 
Also would like to have different cell colours for the ranges of the value...e.g..red for values ranging from 1-10, Orange for 10-45 etcl... can this be done? keeping in mind it will need to be updated easily..
 
Disclaimer - there may be easy ways of doing this, but this is what I use.....
(you'll need to change a fair bit of it)

The auto sort would be easy to do with a vb macro assigned to sheetchange i.e....

Private Sub Workbook_SheetChange(ByVal Sh As Object, _
ByVal Source As Range)

Worksheets("Sheet1").Range("G4:G23").Sort _
Key1:=Worksheets("Sheet1").Range("G4")

this may be over complex and its not tested but should give you some hints.....

For Each cell In Sheets(chartname(NAMEOFSHEET)).Range("G4:G23")

If cell.Value < 10 and cell.Value > 0Then
........cell.Interior.ColorIndex = 6
........cell.Interior.Pattern = xlSolid
Else
........cell.Interior.ColorIndex = 3
........cell.Interior.Pattern = xlSolid
End If

Next cell

end sub
 
Last edited:
So to start off, view code of the worksheet, i have pasted this..



Private Sub Workbook_SheetChange(ByVal Sh As Object, _
ByVal Source As Range)

Worksheets("Sheet1").Range("G4:G23").Sort _
Key1:=Worksheets("Sheet1").Range("G4")



End Sub



Do i need to amend anything to that?

I want to get the autosort working first..
 
QUOTE]
Made a small mistake. Paste this instead...

Private Sub Worksheet_Change(ByVal Target As Range)
Worksheets("Sheet1").Range("G4:G23").Sort _
Key1:=Worksheets("Sheet1").Range("G4")

End Sub


that will work when you update it, but only for the range g4:g23 if you want more sorted use g4:g9999 e.t.c..
 
Still dont work...Iv added that code to the sheet1 code, saved it, tried updating a cell and it dont work..

Do i need to amend any of that code you gave me??
 
Have you definitely added it to the sheet1 code ?

It works for me.

or you could add...

Worksheets("Sheet1").Range("G4:G23").Sort _
Key1:=Worksheets("Sheet1").Range("G4")

to the sheet1 sheet object - under worksheet and then change.

Is your sheet called sheet1 ?

what version of excel are you using ?
 
[Sniper][Wolf] said:
Yep its defenalty right, im using MS Office 2003 with Excel 11.53...
Click to expand...

Are you getting any error messages then ?

If not, then the code is probably in the wrong place. You could try and put a message box in the code to see if the message comes up. If not, then it's definitely in the wrong place or macros are disabled.

I've sent a mail to address in your trust.
 
Last edited:
For anyone interested...

Worksheets("Sheet1").Range("G4:G23").Sort _
Key1:=Worksheets("Sheet1").Range("G4")

should be....

Worksheets("Sheet1").Range("A4:G23").Sort _
Key1:=Worksheets("Sheet1").Range("G4")

which will sort the whole sheet according to column G which is what the OP wanted.
 
Conditional formatting is probably best achieved using "Conditional Formatting" on the Formatting menu.

It's quite simple to use and can be copied and pasted about as well.
 
Buttons said:
Conditional formatting is probably best achieved using "Conditional Formatting" on the Formatting menu.

It's quite simple to use and can be copied and pasted about as well.
Click to expand...


I never knew that ! Can you apply it to a whole row though according to a cell value ?
 
You must log in or register to reply here.
Back
Top Bottom