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.
To fulfill the demand for quickly locating and searching documents.
It is intelligent file search solution for home and business.
Related searches
- python dictionary sort by values descending
- python list sort by key
- python dict sort by value
- python df sort by column
- pandas sort by value
- python sort by multiple columns
- pandas sort by column names
- python sort by value
- python sort by two columns
- sort by columns pandas
- sort by column python
- python sort by two keys