There is no built-in way to sort your cells (or rows) using their colour formatting.
However it is possible by inserting an extra column and then using a user defined function.
The user defined function will return a number indicating the colour of the cell used in that particular cell (or row).
You can then use the normal (Data > Sort) command using this new column as your first sorting column.
Lets assume that you have a simple table of data and that the formatting has been applied manually (NOT using Conditional Formatting).
You must first add another column to your table of data.
This extra column will contain a user defined function which can be used to indicate the order in which to sort the rows.
The "manual" formatting you can use on cells is quite limited and you can only choose from the 52 colours on the colour palette.
You can either change the background colour or you can change the colour of the text.
If you want to sort using the background colour of the cells you can refer to the Interior property of the Range object.
If you want to sort using the colour of the text you can use the Font property of the Range object.
User Defined Function
This user defined function will return the colour index of the cell reference which is passed as the first argument.
The number returned will always be between 1 and 52 since this corresponds to the location on the colour palette.
Once the user defined function has been entered into a code module in the corresponding VBA Project you can use it in your extra column.
If the cell does not contain any manual formatting then 0 is returned.
Public Function ColourSorting(ByVal rgeCell As Range, _
ByVal bBackGround As Boolean, _
ByVal bText As Boolean) As Integer
If bBackGround = True Then ColourSorting = rgeCell.Interior.ColorIndex
If bText = True Then ColourSorting = rgeCell.Font.ColorIndex
If ColourSorting < 1 Then ColourSorting = 0
Sorting by Background Colour
Sorting by Text Colour
Using Conditional Formatting
If you want to sort a table of data based on the formatting applied by conditional formatting this is slightly more complicated.
For more details please refer to the Sorting By Conditional Formatting page.
© 2023 Better Solutions Limited. All Rights Reserved. © 2023 Better Solutions Limited TopPrevNext