Sorting information with Color Cells Microsoft Excel 2003

**Page001** · 11-25-2009, 04:48 PM

Hello Everybody,

I am using Microsoft Excel 2003.

I put color in my cells to show contrast with the information in my data list. I used blue and white for my cell colors. When I used sort to put my information in alphabetical order it did not only rearrange the information, it also rearranged the colored cells. My orginal pattern (before using sort) was blue, white, blue, white, blue, white. After I used sort, the pattern was changed to white, white, blue, white, blue, blue.

Is there any way I can use sort to arrange my information only in alphabetical order, using sort, without rearranging the color of the cells?

If anybody knows, please answer and thank you in advance.

**Paul** · 11-25-2009, 04:55 PM

Hi Page, and welcome to the forum.

Are you simply using the colors to act as shaded alternating rows so it's easier to read across, or are the colors based on the data in those cells?

If you're using it just to alternate row colors, I suggest you use Conditional Formatting to change the background color of your cells rather than regular cell formatting.

To do so, remove the current colors in your cells, then select your range of cells (for example, A2:G50). Open the Conditional Formatting dialog and select the option to use a formula. Enter the following formula, then set the color you want (e.g. Blue) for the alternating rows.

=MOD(ROW(),2)=0

If you wanted to assign a white background to the alternating cells (as opposed to the default no-fill background), you could create a second condition using the formula:

=MOD(ROW(),2)=1

and choose White as the background fill color. This would create alternating Blue and White rows.

Hope that helps!

**Page001** · 11-25-2009, 07:04 PM

Thanks for replying Paul!

Yes I am using the colors to act as shaded alternating rows so it's easier to read across.

I read your response. Let's forget about the colors of blue and white. Let's say I am using blue and yellow instead.

EXAMPLE

Smith, Bob (in a blue shaded cell)
Doe, Jane (in a yellow shaded cell)
Quincy, John (in a blue shaded cell)
Public, Sue (in a yellow shaded cell)

Now how do I get results of the putting those names in alphabetical order and keeping the shaded cell arrangement. THE RESULTS

Doe, Jane (in a blue shaded cell)
Public, Sue (in a yellow shaded cell)
Quincy, John (in a blue shaded cell)
Smith, Bob (in a yellow shaded cell)

Thanks for your help

**Paul** · 11-25-2009, 07:08 PM

Hi Page,

I explained how to create the alternating row colors in my post:

To do so, remove the current colors in your cells, then select your range of cells (for example, A2:G50). Open the Conditional Formatting dialog and select the option to use a formula. Enter the following formula, then set the color you want (e.g. Blue) for the alternating rows.

=MOD(ROW(),2)=0

If you wanted to assign a *yellow* background to the alternating cells (as opposed to the default no-fill background), you could create a second condition using the formula:

=MOD(ROW(),2)=1

and choose *yellow* as the background fill color. This would create alternating Blue and *Yellow* rows.

**Page001** · 11-25-2009, 07:09 PM

Thanks I'll try it and let you know if I got it right.

**Page001** · 12-09-2009, 09:55 AM

Hello Paul, Sorry it took so long to reply. Turkey day came up, got busy and now I am even more busy before Ham day comes. Your solution did work. Thank you for your help.