Sort rows by color

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Chris - 27 Jun 2007 12:58 GMT

Is there a way in office 2000 to sort rows by color? I'm not good with code
and cutting and pasting over 1000 rows is becoming tiresome.

Thanks

Reply to this Message

Bernie Deitrick - 27 Jun 2007 14:25 GMT

Text color or background color?

Use a UDF - copy and paste this into a codemodule:

Function BGColor(myR As Range) As Integer
BGColor = myR.Interior.ColorIndex
End Function
Function FColor(myR As Range) As Integer
FColor = myR.Font.ColorIndex
End Function

Then use them like so

=BGColor(A2)
=FColor(A2)

depending on whether you want BackGroundColor or FontColor.

Copy the formula down to match your cells, then sort based on that column.

HTH,
Bernie
MS Excel MVP

> Is there a way in office 2000 to sort rows by color? I'm not good with code
> and cutting and pasting over 1000 rows is becoming tiresome.
>
> Thanks

Reply to this Message

Chip Pearson - 27 Jun 2007 15:07 GMT

Chris,

Excel 2007 added this capability. For earlier versions, see
http://www.cpearson.com/excel/SortByColor.htm for example code to do this.

Signature

Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting
www.cpearson.com
(email on the web site)

> Is there a way in office 2000 to sort rows by color? I'm not good with
> code
> and cutting and pasting over 1000 rows is becoming tiresome.
>
> Thanks

Reply to this Message

Rate this thread: