Method 1: If you have Excel 2007 or later…
Everything is simple here. Starting from the 2007 version, Excel added sorting and filtering by fill color and font color as a standard function. The easiest way to get to them is through a standard autofilter:
Of the minuses, only the inability to filter by several colors at once.
Method 2: If you have Excel 2003 or later…
Versions of Microsoft Excel prior to 2007 in their original state did not know how to sort cells by format, which is certainly a serious drawback if you use color coding in your spreadsheets (and this can be convenient). Therefore, let’s correct the annoying omission – we will write a simple user-defined function in VBA ColorIndex, which will output the numeric fill color code of any given cell. According to this code, we will further sort.
To do this, open the Visual Basic Editor via the menu Service – Macro – Visual Basic Editor (Tools — Macro — Visual Basic Editor), insert a new empty module (menu Insert – Module) and copy the text of a simple function there:
Public Function ColorIndex(Cell As Range) ColorIndex = Cell.Interior.ColorIndex End Function
Now you can close the Visual Basic editor, return to Excel and, having selected any empty cell, call the created function ColorIndex through the menu Insert – Function — category User Defined (Insert — Function — User defined). As an argument, specify the cell whose fill color you want to get as a numeric code.
With regard to lists, such a function will make it easy to sort cells by fill color:
If you need to pull out not the fill color code, but the font color code, then the function will change slightly:
Public Function ColorIndex(Cell As Range) ColorIndex = Cell.Font.ColorIndex End Function
PS
Our ColorIndex function unfortunately has a couple of drawbacks:
- It cannot get the color that a cell has when using conditional formatting.
- It is not automatically recalculated when you change the color of one of the cells, because Excel does not consider the color change to be an edit to the contents of the cell and does not trigger an automatic worksheet recalculation. You need to do it yourself by clicking Ctrl+Alt+F9, or by adding to our function in each cell the following additive:
=ColorIndex(A2)+TODAY()*0
so that the contents of the cell are automatically recalculated each time the sheet is recalculated.
- One-button sorting by color (PLEX add-on)
- Sum Cells with a Specific Fill Color or Font Color (PLEX Add-on)
- What is a macro, where to insert macro code in VBA, how to use them