Sort by colour coding

Posted by Len Clarke on August 21, 2001 9:06 AM

I have a list with various items of information about customers held in each row eg A1=Surname, A2=Chritian name, A3=Address
One of my staff helpfully filled each row in a colour which identified the product each had purchased eg all purchasers of Product X are coloured Blue, Product Y=Yellow etc. However, there are no details on the worksheet, other than the colour that identify which product they purchased.

So, the question is, can I sort the data by the colour used to infill the cells?

Posted by Aladin Akyurek on August 21, 2001 9:40 AM

Add the following UDF to your workbook (via Insert|Module after activating Visual Basic Editor).

Function FILLCOLOR(cell) As Variant
'
' J. Walkenbach
'
Application.Volatile True
FILLCOLOR = cell.Range("A1").Interior.ColorIndex
End Function

This UDF produces a numeric code for background color (I assumed that you have cells whose backgrounds are colored). Sort your data by the column where color codes are.

Aladin

Posted by Len on August 21, 2001 10:35 AM

Thanks for that.
Followed the instructions and pasted your UDF into a new module. However, when I select the range of cells that have various colours in them, the FILLCOLOR function only applies a numerical value to the first cell and leaves all other selected cells blank.

Have I misunderstood something? J. Walkenbach

Posted by Aladin Akyurek on August 21, 2001 11:02 AM

This UDF can only applied per cell.

I applied it to range B1:B14 that has variously colored cells. That is, I entered in C1

=FILLCOLOR(B1)

and copied down this formula up to B14. I get the following results:

{-4142;
6;
3;
43;
6;
33;
44;
38;
5;
-4142;
5;
-4142;
-4142;
5}

Sorted the range B1:C14 by column C, ascending. It just works.

Aladin

============= J. Walkenbach