MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Sort text by color??


Posted by Jason on February 01, 2002 1:59 PM

Is there a way to sort by color? I have two sets of data that I have compared to each other by pasting one set of data under the other, colored one set red, then sorted to place like data next to each other. Now that I have done this, I need to get rid of all the "red" data.

Any suggestions?

Thank you in advance....
Jason


Posted by Jack in the UK on February 01, 2002 2:05 PM

Yes see www.ozgrid.com Dave cover tis in VBA section [NT]

Posted by Jason on February 01, 2002 2:30 PM

After going to Ozgrid, which is now a new source, thank you, and researching this issue, it looks as though you can only do this if the cell itself is colored. My problem is that the text within the cell is colored and not the cell.

Any other suggestions?

Thanks again....

Posted by Jack in the UK on February 01, 2002 2:46 PM

Jason

this can be done im sure i have code somwhere that add by text colour... so add that to sort or filter should work..

Ill have a play but 11pm and not good to play now ... ill try and post if i sus it..

HTH
Jack

Posted by Ivan F Moala on February 01, 2002 4:39 PM

Do as dave has suggested but use the UDF
for the fonts;

Function ColourRankFont(ColorOrder As Range, LookCell As Range)
'''''''''''''''''''''''''''''''''''''''''''''
'Written by OzGrid Business Applications
'www.ozgid.com

'Ranks a list of Colors so they can be sorted
'Amended for Font colours 2/2/2002
'''''''''''''''''''''''''''''''''''''''''''''

Dim i As Integer
Dim ICol1 As Integer
Dim ICol2 As Integer

i = 1
ICol2 = -1
ColourRankFont = 0

'Loop until match is found
Do Until ICol1 = ICol2
ICol1 = ColorOrder(i, 1).Font.ColorIndex
ICol2 = LookCell.Font.ColorIndex
If i = ColorOrder.Rows.Count + 1 Then
'No Match found place in Text
ColourRankFont = "No colour match!!!"
Exit Do
End If
'Pass the Row number of the colour match
ColourRankFont = i
i = i + 1
Loop
End Function

HTH

Ivan After going to Ozgrid, which is now a new source, thank you, and researching this issue, it looks as though you can only do this if the cell itself is colored. My problem is that the text within the cell is colored and not the cell.