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