sorting

C

CoCoa

When I have the worksheet filled with different colors to distinguish certain
things it will not sort alphabetically. Can anyone help?
 
K

Ken Johnson

Hi CoCoa,
Do you mean the values are not sorting alphabetically or the colors are
not moving when the values are sorted?

How did you color the cells?
Did you just change their fill color or did you use conditional
formatting?
If you just changed their fill color then the colors will not be
affected by the sort.
If you used conditional formatting the colors will change according to
the result of the sort.

Ken Johnson
 
K

Ken Johnson

Hi CoCoa,
Oops!

I honestly thought that fill color would not be affected by sorting.
After trying it out I have to ever so humbly retract that statement.
Sorry about that.

Ken Johnson
 
C

CoCoa

I don't understand it either because I need to figure this out for my
business. I think that I might contact microsoft office for a solution. I
still have one free time to contact them.
 
K

Ken Johnson

Hi CoCoa,
So you want the fill color to be unaffected by the sort?
I'd like to know what you find out?
Ken Johnson
 
K

Ken Johnson

Hi CoCoa,
If I'm correct in assuming that you want the cells' fill colors not to
be affected by the sorting then one way is to use conditional
formatting.

Say you want A1 to have a yellow fill, then go:

Format>Conditional Formatting...> select "Formula Is" in left box>
type "=OR(NOT(ISBLANK($A$1)), ISBLANK($A$1))" in the right box
(without the speech marks)> Click on the Format... button>select yellow
fill then click OK.

This over-rides the normal fill color that moves when sorting.

The Formula used is true regardless of what type of data is in A1.

Tedious aye.
Ken Johnson
 
K

Ken Johnson

Hi CoCoa,
Forget about the unnecessary formula, use "=1=1" instead. This too is
always TRUE and works just as well as "=OR(NOT(ISBLANK($A$1)),
ISBLANK($A$1))". The latter formula is very good for toning up your
finger muscles ;-)
Ken Johnson
 
K

Ken Johnson

Hi CoCoa,
This should earn me the "Pillock of the Year" award!
All you need to type in the Formula Is box is =1

Also, I wouldn't mind betting this is not really what you are trying to
do anyway!

Ken Johnson
 
Top