nicnad
04-23-2012, 09:26 AM
Hi,
I have the following code :
Sub sortonrisk()
Dim rng As Range
ActiveSheet.AutoFilterMode = False
LastRow = ActiveSheet.Cells.Find(What:="*", After:=[A1], SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
lastcol = ActiveSheet.Cells(2, Columns.Count).End(xlToLeft).Column
Set rng = Range("b2:b" & LastRow)
ActiveSheet.Range("a2:" & Cells(LastRow, lastcol).Address).AutoFilter
With ActiveSheet.AutoFilter.Sort
With .SortFields
.Clear
.Add(rng, 1, 1, , 0).SortOnValue.Color = 0 'black
.Add(rng, 1, 1, , 0).SortOnValue.Color = RGB(99, 37, 35) 'dark red
.Add(rng, 1, 2, , 0).SortOnValue.Color = RGB(0, 176, 80) 'green
.Add(rng, 1, 2, , 0).SortOnValue.Color = vbYellow
.Add(rng, 1, 2, , 0).SortOnValue.Color = vbRed
.Add rng, 0, 1, 0
End With
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
The code works great in Excel 2007 but generates a runtime error 438 in Excel 2003. I know that the Sort object are not compatible in Excel 2003. How do I transform my code so that I use Range().Sort instead?
What I want the result to look like is to have row sorted so that cells filled in black are first, dark red second, red third, yellow 4th, green 5th and then sort on text value A to Z.
Can this be done in Excel 2003?
I don't have Excel 2003 on my workstation so I cannot use the macro recorder in order to reproduce the right code.
Could someone please help me write the Excel 2003 compatible code of the above?
Your help and time are really appreciated.
Regards.
I have the following code :
Sub sortonrisk()
Dim rng As Range
ActiveSheet.AutoFilterMode = False
LastRow = ActiveSheet.Cells.Find(What:="*", After:=[A1], SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
lastcol = ActiveSheet.Cells(2, Columns.Count).End(xlToLeft).Column
Set rng = Range("b2:b" & LastRow)
ActiveSheet.Range("a2:" & Cells(LastRow, lastcol).Address).AutoFilter
With ActiveSheet.AutoFilter.Sort
With .SortFields
.Clear
.Add(rng, 1, 1, , 0).SortOnValue.Color = 0 'black
.Add(rng, 1, 1, , 0).SortOnValue.Color = RGB(99, 37, 35) 'dark red
.Add(rng, 1, 2, , 0).SortOnValue.Color = RGB(0, 176, 80) 'green
.Add(rng, 1, 2, , 0).SortOnValue.Color = vbYellow
.Add(rng, 1, 2, , 0).SortOnValue.Color = vbRed
.Add rng, 0, 1, 0
End With
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
The code works great in Excel 2007 but generates a runtime error 438 in Excel 2003. I know that the Sort object are not compatible in Excel 2003. How do I transform my code so that I use Range().Sort instead?
What I want the result to look like is to have row sorted so that cells filled in black are first, dark red second, red third, yellow 4th, green 5th and then sort on text value A to Z.
Can this be done in Excel 2003?
I don't have Excel 2003 on my workstation so I cannot use the macro recorder in order to reproduce the right code.
Could someone please help me write the Excel 2003 compatible code of the above?
Your help and time are really appreciated.
Regards.