Q Is it possible to sort a range of cells based on the cell's formatting? In my Excel worksheet, I colour-code various cells in a range by changing their background colour. I would like to be able to sort the range by these background colours, so cells of the same colour are together.

A I don't know of any spreadsheet that allows sorting by cell formatting. But with the help of a simple custom function, you can do it. Creating the function involves inserting a Visual Basic module into your workbook, then using that function in worksheet formulas. Here's what to do.

1. In Excel 97, select ToolsûMacroûVisual Basic Editor; then select InsertûModule and enter the VBA code below. (If you're using Excel 5 or Excel 95, select InsertûMacroûModule.)

This function returns the colour value of a cell's background. Select FileûClose and Return to Microsoft Excel. In earlier versions of Excel, just click the tab to return to your worksheet.

2. Next, insert a column of formulas containing this function. For example, I entered the following formula in C2 and copied it to C3:C14: =CellFormat(B2)

The formulas in Column C return the colour value of each cell in Column B.

3. Sort your data by the column that contains the CellFormat formulas. To get the results shown in the figure, place the pointer anywhere in Column C and click the Sort Ascending button.

If you use Excel 97's new conditional formatting feature, the function will always return the value of the cell's original background colour, regardless of what's displayed.

You can modify the CellFormat function to return other types of formatting data too, but you'll need to know a bit about the VBA properties first. For example, to sort by Bold formatting, change the second statement in the function to:

After you make this change, the CellFormat function will return True or False.

Caption: A custom function used in a simple formula returns the colour value of a cell

Caption: These are the results obtained when the data is sorted by the column that contains the CellFormat formulas