Sort by color row
 

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.

Sort by color row
 

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.
--
Hope this helps
Martin Fishlock, Bangkok, Thailand
Please do not forget to rate this reply.


"Jeff" wrote:

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.

Sort by color row
 

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

"Martin Fishlock" wrote:

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.
--
Hope this helps
Martin Fishlock, Bangkok, Thailand
Please do not forget to rate this reply.


"Jeff" wrote:

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.

Sort by color row
 

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.

--
Hope this helps
Martin Fishlock, Bangkok, Thailand
Please do not forget to rate this reply.


"Jeff" wrote:

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

"Martin Fishlock" wrote:

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.
--
Hope this helps
Martin Fishlock, Bangkok, Thailand
Please do not forget to rate this reply.


"Jeff" wrote:

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.

Sort by color row
 

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


"Martin Fishlock" wrote:

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.

--
Hope this helps
Martin Fishlock, Bangkok, Thailand
Please do not forget to rate this reply.


"Jeff" wrote:

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

"Martin Fishlock" wrote:

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.
--
Hope this helps
Martin Fishlock, Bangkok, Thailand
Please do not forget to rate this reply.


"Jeff" wrote:

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.

Sort by color row
 

No problems

martin_fishlock at yahoo.co.uk
--
Hope this helps
Martin Fishlock, Bangkok, Thailand
Please do not forget to rate this reply.


"Jeff" wrote:

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


"Martin Fishlock" wrote:

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.

--
Hope this helps
Martin Fishlock, Bangkok, Thailand
Please do not forget to rate this reply.


"Jeff" wrote:

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

"Martin Fishlock" wrote:

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.
--
Hope this helps
Martin Fishlock, Bangkok, Thailand
Please do not forget to rate this reply.


"Jeff" wrote:

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.

Sort by color row
 

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

--
Hope this helps
Martin Fishlock, Bangkok, Thailand
Please do not forget to rate this reply.


"Martin Fishlock" wrote:

No problems

martin_fishlock at yahoo.co.uk
--
Hope this helps
Martin Fishlock, Bangkok, Thailand
Please do not forget to rate this reply.


"Jeff" wrote:

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


"Martin Fishlock" wrote:

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.

--
Hope this helps
Martin Fishlock, Bangkok, Thailand
Please do not forget to rate this reply.


"Jeff" wrote:

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

"Martin Fishlock" wrote:

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.
--
Hope this helps
Martin Fishlock, Bangkok, Thailand
Please do not forget to rate this reply.


"Jeff" wrote:

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.