Sort rows by colors

G

Guest

Good Morning,
Can you please help I have a very large spreadsheet that has 3 different
colors in the rows and I have to sort them by color. Is there any way to do
that?
Exampl:
Store Number Street Address Color on each row
000111 676 STATE STREET Yellow
000115 2649 E 79TH STREET White
000118 5650 W BELMONT blue
000120 3302 W BELMONT Yellow
 
N

Niek Otten

http://www.cpearson.com/excel/SortByColor.htm

--
Kind regards,

Niek Otten
Microsoft MVP - Excel

| Good Morning,
| Can you please help I have a very large spreadsheet that has 3 different
| colors in the rows and I have to sort them by color. Is there any way to do
| that?
| Exampl:
| Store Number Street Address Color on each row
| 000111 676 STATE STREET Yellow
| 000115 2649 E 79TH STREET White
| 000118 5650 W BELMONT blue
| 000120 3302 W BELMONT Yellow
|
|
 
B

Bob Flanagan

One additional suggestion, assuming that you use Chip's code. If you modify
the colors, press ALT-CTL-F9. This forces a recalc of the functions. Excel
will not recalc the color functions, as a change in color does not trigger
calculation.

Bob Flanagan
Macro Systems
http://www.add-ins.com
Productivity add-ins and downloadable books on VB macros for Excel
 
G

Guest

Thank you. I followed the instructions that Niek provided but I am getting a
error message as #NAME?
Am I doing anything wrong?, I starting on cell A2 and I put this formula
=ColorIndexOfCell(A2,FALSE,TRUE), please help.
 
N

Niek Otten

Did you put the code in a standard module?
See these instructions:

================================================
Pasting a User Defined Function (UDF)
Niek Otten, March 31, 2006

If you find a VBA function on the Internet or somebody mails you one, and you don't know how to implement it, follow these
steps:

Select all the text of the function.
CTRL+C (that is, press and hold down the CTRL key, press C, release both). This a shortcut for Copy.
Go to Excel. Press ALT+F11 (same method: press and hold the ALT key, press the F11 key and release both). You are now in the
Visual Basic Editor (VBE).
From the menu bar, choose Insert>Module. There should now be a blank module sheet in front of you. Click in it and then
press CTRL+V (same method.). This a shortcut for Paste. You should now see the text of the function in the Module.
Press ALT+F11 again to return to your Excel worksheet.
You should now be able to use the function as if it were a built-in function of Excel, like =SUM(..)
================================================


--
Kind regards,

Niek Otten
Microsoft MVP - Excel

| Thank you. I followed the instructions that Niek provided but I am getting a
| error message as #NAME?
| Am I doing anything wrong?, I starting on cell A2 and I put this formula
| =ColorIndexOfCell(A2,FALSE,TRUE), please help.
|
|
| "Bob Flanagan" wrote:
|
| > One additional suggestion, assuming that you use Chip's code. If you modify
| > the colors, press ALT-CTL-F9. This forces a recalc of the functions. Excel
| > will not recalc the color functions, as a change in color does not trigger
| > calculation.
| >
| > Bob Flanagan
| > Macro Systems
| > http://www.add-ins.com
| > Productivity add-ins and downloadable books on VB macros for Excel
| >
| > | > > http://www.cpearson.com/excel/SortByColor.htm
| > >
| > > --
| > > Kind regards,
| > >
| > > Niek Otten
| > > Microsoft MVP - Excel
| > >
| > > | > > | Good Morning,
| > > | Can you please help I have a very large spreadsheet that has 3 different
| > > | colors in the rows and I have to sort them by color. Is there any way to
| > > do
| > > | that?
| > > | Exampl:
| > > | Store Number Street Address Color on each row
| > > | 000111 676 STATE STREET Yellow
| > > | 000115 2649 E 79TH STREET White
| > > | 000118 5650 W BELMONT blue
| > > | 000120 3302 W BELMONT Yellow
| > > |
| > > |
| > >
| > >
| >
| >
| >
 
G

Guest

It works. Thank you so much.

Niek Otten said:
Did you put the code in a standard module?
See these instructions:

================================================
Pasting a User Defined Function (UDF)
Niek Otten, March 31, 2006

If you find a VBA function on the Internet or somebody mails you one, and you don't know how to implement it, follow these
steps:

Select all the text of the function.
CTRL+C (that is, press and hold down the CTRL key, press C, release both). This a shortcut for Copy.
Go to Excel. Press ALT+F11 (same method: press and hold the ALT key, press the F11 key and release both). You are now in the
Visual Basic Editor (VBE).
From the menu bar, choose Insert>Module. There should now be a blank module sheet in front of you. Click in it and then
press CTRL+V (same method.). This a shortcut for Paste. You should now see the text of the function in the Module.
Press ALT+F11 again to return to your Excel worksheet.
You should now be able to use the function as if it were a built-in function of Excel, like =SUM(..)
================================================


--
Kind regards,

Niek Otten
Microsoft MVP - Excel

| Thank you. I followed the instructions that Niek provided but I am getting a
| error message as #NAME?
| Am I doing anything wrong?, I starting on cell A2 and I put this formula
| =ColorIndexOfCell(A2,FALSE,TRUE), please help.
|
|
| "Bob Flanagan" wrote:
|
| > One additional suggestion, assuming that you use Chip's code. If you modify
| > the colors, press ALT-CTL-F9. This forces a recalc of the functions. Excel
| > will not recalc the color functions, as a change in color does not trigger
| > calculation.
| >
| > Bob Flanagan
| > Macro Systems
| > http://www.add-ins.com
| > Productivity add-ins and downloadable books on VB macros for Excel
| >
| > | > > http://www.cpearson.com/excel/SortByColor.htm
| > >
| > > --
| > > Kind regards,
| > >
| > > Niek Otten
| > > Microsoft MVP - Excel
| > >
| > > | > > | Good Morning,
| > > | Can you please help I have a very large spreadsheet that has 3 different
| > > | colors in the rows and I have to sort them by color. Is there any way to
| > > do
| > > | that?
| > > | Exampl:
| > > | Store Number Street Address Color on each row
| > > | 000111 676 STATE STREET Yellow
| > > | 000115 2649 E 79TH STREET White
| > > | 000118 5650 W BELMONT blue
| > > | 000120 3302 W BELMONT Yellow
| > > |
| > > |
| > >
| > >
| >
| >
| >
Click to expand...
 
N

Niek Otten

You're welcome!

--
Kind regards,

Niek Otten
Microsoft MVP - Excel

| It works. Thank you so much.
|
| "Niek Otten" wrote:
|
| > Did you put the code in a standard module?
| > See these instructions:
| >
| > ================================================
| > Pasting a User Defined Function (UDF)
| > Niek Otten, March 31, 2006
| >
| > If you find a VBA function on the Internet or somebody mails you one, and you don't know how to implement it, follow
these
| > steps:
| >
| > Select all the text of the function.
| > CTRL+C (that is, press and hold down the CTRL key, press C, release both). This a shortcut for Copy.
| > Go to Excel. Press ALT+F11 (same method: press and hold the ALT key, press the F11 key and release both). You are now in
the
| > Visual Basic Editor (VBE).
| > From the menu bar, choose Insert>Module. There should now be a blank module sheet in front of you. Click in it and then
| > press CTRL+V (same method.). This a shortcut for Paste. You should now see the text of the function in the Module.
| > Press ALT+F11 again to return to your Excel worksheet.
| > You should now be able to use the function as if it were a built-in function of Excel, like =SUM(..)
| > ================================================
| >
| >
| > --
| > Kind regards,
| >
| > Niek Otten
| > Microsoft MVP - Excel
| >
| > | Thank you. I followed the instructions that Niek provided but I am getting a
| > | error message as #NAME?
| > | Am I doing anything wrong?, I starting on cell A2 and I put this formula
| > | =ColorIndexOfCell(A2,FALSE,TRUE), please help.
| > |
| > |
| > | "Bob Flanagan" wrote:
| > |
| > | > One additional suggestion, assuming that you use Chip's code. If you modify
| > | > the colors, press ALT-CTL-F9. This forces a recalc of the functions. Excel
| > | > will not recalc the color functions, as a change in color does not trigger
| > | > calculation.
| > | >
| > | > Bob Flanagan
| > | > Macro Systems
| > | > http://www.add-ins.com
| > | > Productivity add-ins and downloadable books on VB macros for Excel
| > | >
| > | > | > | > > http://www.cpearson.com/excel/SortByColor.htm
| > | > >
| > | > > --
| > | > > Kind regards,
| > | > >
| > | > > Niek Otten
| > | > > Microsoft MVP - Excel
| > | > >
| > | > > | > | > > | Good Morning,
| > | > > | Can you please help I have a very large spreadsheet that has 3 different
| > | > > | colors in the rows and I have to sort them by color. Is there any way to
| > | > > do
| > | > > | that?
| > | > > | Exampl:
| > | > > | Store Number Street Address Color on each row
| > | > > | 000111 676 STATE STREET Yellow
| > | > > | 000115 2649 E 79TH STREET White
| > | > > | 000118 5650 W BELMONT blue
| > | > > | 000120 3302 W BELMONT Yellow
| > | > > |
| > | > > |
| > | > >
| > | > >
| > | >
| > | >
| > | >
| >
| >
| >
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

How do I sort rows by color? 1
How to sort rows by Colors 5
Sorting Rows By Colors 1
How can I sort data and have the highest value red, 2nd blue, ect 3
Sort data by formatting 2
In Excel it would help a lot to be able to sort rows by color 7
Can Excel sort rows by color? 2
Sorting rows by colors (categories) 1

Top