Excel 2003 Sortfields VBA runtime error 438

[nicnad]

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.

[Bob Phillips]

You cannot sort by colour in Excel 2003. You would have tgo have a helper column and a UDF to determine the colorindex, then sort on that.

[nicnad]

Thank you for the tip.

I will try to write that UDF and post back the solution.

Regards

[Bob Phillips]

I forgot that I had covered this on one of my web pages http://www.xldynamic.com/source/xld.ColourCounter.html#sorting