BET: Microsoft Excel Cells & Ranges

Leading the way in Microsoft Office Development

Home

Excel

Word

Microsoft Excel > Cells & Ranges > Sorting By Colour

< Previous | Next >

Using Manual Formatting

	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.

Additional 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.

Manual Formatting

	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.

1	Range("C3").Interior.ColorIndex

If you want to sort using the colour of the text you can use the Font property of the Range object.

2	Range("C3").Font.ColorIndex

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.

3
4
5
6
7
8
9
10
11
12

Option Explicit

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
End Function

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.

< Previous | Top | Next >

Leading the way in Microsoft Office Development

Using Manual Formatting

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.

Additional 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.

Manual Formatting

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.

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.