Sort by color row

J

Jeff

Hi,

I have an excel list where some rows or fields are highlighted in Red. How
do I sort and group all the rows highlighted in Red together ?

For example:
A
1 SKE
2 SDF
3 SOK
4 UEE
5 EIO
6 LKE
7 POE

SKE, EIO, UEE fields are highlight in Red. Sort in a way that SKE, EIO, UEE
will display in top 3 row. Thanks.
On the other hand, how do we sort if the text is highlighted in BOLD.
 
M

Martin Fishlock

The easiest way to do it is to add an extra column on the end and put the
following in it
=if(or(A1="SKE",A1="EIO",A1="UEE"),1,0)
copy it down


and then sort on column b and then a.

you can then use autofile to select those three items.
 
J

Jeff

Martin,

I think you misunderstand my requirement. It should be sort by colour. All
the rows highlighted in colours should be group together.

Thanks.
Jeff
 
M

Martin Fishlock

You cannot normally sort by cell formatting like colours or bold or italic.

What you did mention was that some rows namely SKE, EIO, UEE were the one in
red so in the new column put a key to say that it was one of the items and
then sort on the key and then the original items.

Please explain more. As the only other way is to use a macro to sort on the
colours but you need to also need to use a working column.
 
J

Jeff

Martin,
I could send you an excel example and also how it should look like after
sorting to your e-mail if you don't mind.

Thanks..Jeff
 
M

Martin Fishlock

For interest purposes, this is the code that I sent.

Sub DoPatternSort()

Dim rRange As Range
Dim lRowStart As Long, lRowEnd As Long, lRow As Long
On Error GoTo ErrorEnd
Application.ScreenUpdating = False
Set rRange = Selection.CurrentRegion
lRowStart = rRange.Row + 1
lRowEnd = rRange.Rows.Count

rRange.Cells(1, 2).EntireColumn.Insert

For lRow = lRowStart To lRowEnd Step 1
rRange.Cells(lRow, 2) = rRange.Cells(lRow, 1).Interior.ColorIndex
Next lRow

rRange.Sort Key1:=rRange.Cells(2, 2), Order1:=xlDescending, _
Key2:=rRange.Cells(2, 3), Order2:=xlAscending, _
Key3:=rRange.Cells(2, 1), Order3:=xlAscending, _
Header:=xlYes, OrderCustom:=1, MatchCase:=False, _
Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal, _
DataOption2:=xlSortNormal, _
DataOption3:=xlSortNormal
rRange.Cells(1, 2).EntireColumn.Delete
ErrorEnd:
Application.ScreenUpdating = True
End Sub
 
Top