Sort by Color



Sort by Color*

I generally subscribe to the notion that we who use Excel to analyze data should not be writing code. However, I found this very simple visual basic script and I can’t resist passing it on. Excel does not allow sorting by color, but often we receive workbooks that are color coded and our lives would be easier if we could sort the colors. This is a simple macro that finds the color of the background or font and displays as a number. The numbers can then be sorted. This will not work with conditionally formatted cells because conditional formats do not have the cell property changed... so it remains as it was before conditional formatting was applied. It works just fine where the color has been added through the Fill Color or Font Color.

Navigation: Tools > Macro > Visual Basic Editor

Click: This Workbook

Insert > Module. In the Module Editor type:

Function GetColor (Mycell As Range)

GetColor = Mycell.Interior.ColorIndex

End Function

Note: Interior indicates that it is the background you want. For font color, substitute Font for Interior.

[pic]

The End Function is automatically entered when you finish typing your first line.

You do not have to save this file. Just close (X out) the Visual Basic Editor. This is now a custom function that will operate just within the worksheet in which it was created. Also, this means you have created a macro. Worksheet macro security should be set to medium. Medium tells the spreadsheet to ask before activating any macros.

In a cell type: =GetColor(CellReference)

* (The above code is provided by the people at Data Pig Technologies who devised this elegant procedure.)

................
................

In order to avoid copyright disputes, this page is only a partial summary.

Google Online Preview   Download