I have a Large Spreadsheet that I am using the following formula on, Whick Firefytr gave to me, 


Option Explicit 
Sub CheckItemNumbersForJack() 
    Dim cel As Range, rng As Range, tmp 
    Set rng = Sheets("Compare").Range("E12", Sheets("Compare").Range("E65536").End(xlUp)) 
    With Sheets("Numbers increased by Star") 
        For Each cel In rng 
            Set tmp = .Range("A:A").Find(cel.Value, _ 
            lookat:=xlWhole, MatchCase:=True) 
            If Not tmp Is Nothing Then 
                cel.Interior.ColorIndex = 4 
                tmp.Interior.ColorIndex = 4 
            End If 
        Next cel 
    End With 
End Sub

I would like to be able to sort by the hightlighted color of the Cell, I am having a VERY hard time getting the attached file to do this. The file came to me from another department and I noticed that there is some soft of a Condtional format in the Spreadsheet.

I have tryed just about everything, such as Chips solution at http://www.cpearson.com/excel/SortByColor.htm

and someone on Mr. Excel with the following code, 


Sub colorsort() 
Dim c As Range, rng 
Set rng = Range("A1:A" & Range("A65536").End(xlUp).Row) 
Range("A1").EntireColumn.Insert shift:=xlToRight 
For Each c In rng 
If c.Interior.ColorIndex = 35 Then 'depends on which green... (try 50 maybe?) 
    c.Offset(0, -1).Value = 1 
Else 
    c.Offset(0, -1).Value = 2 
End If 
Next c 
Cells.Sort key1:=Range("A2"), order1:=xlAscending, header:=xlGuess, MatchCase:=False 
Range("A1").EntireColumn.Delete shift:=xlToLeft 
End Sub

Hope someone can take a look at this problem and offer some type of soulution.


Thanks Again



Jack