Excel does not (yet) provide facilities for sorting or filtering a range of cells by the colour of the cells, or for counting the instances of a particular colour. It is not difficult, but it does require some hand-crafting.

The generally suggested method of solving this problem is to write a UDF that returns the ColorIndex of a nominated cell, and add a 'helper' range to return the ColorIndex values of the original range. This can be used for both counting and sorting.

This page contains a fully generic UDF to return the colorindex of an array of cells, either the cell's interior colour or its text colour, and shows hoe to deploy that UDF to achieve the above aims. It is structured in the following sections:

Counting Coloured Cells
Summing Coloured Cells
Sorting Coloured Cells
Filtering Coloured Cells
Colour Constants
Constraints
Code Sample(s)
References

If applying any of the techniques described here, it is necessary to copy all of the VBA code below to a standard code module in your workbook, so that the worksheet functions can utilise the Colorindex function.

Counting Coloured Cells

Counting coloured cells, either cell colour or font colour, is easily achieved with the function presented here. One of the major benefits of this routine is that it can utilise standard worksheet functions for summing the colours, without the need for a 'helper' column.

The function has been specifically designed to return an array of colorindex values that can be used in standard worksheet functions, such as SUM. In reality, it is best served by the SUMPRODUCT function to count the instances of a particular colour, using the following technique(s).

=SUMPRODUCT(--(ColorIndex(A1:A100)=3))
counts all red cells (background color) within the range A1:A100

or
=SUMPRODUCT(--(ColorIndex(A1:A100,TRUE)=3))
counts all cells with red text within the range A1:A100

To get the colorindex of a specific cell, simply use
=ColorIndex(A1)

As well as counting all cells with a particular colorindex value, it is possible to use the colour of a cell as the comparison, like this
=SUMPRODUCT(--(ColorIndex(A1:A100)=ColorIndex(A1)))

Summing Coloured Cells

As well as counting the number of cells that meet a particular criteria, there may be a requirement to sum by colour. This could be summing the values of the range of coloured cells, or a range associated with the range of coloured cells.

This is simply achieved by extending the SUMPRODUCT function to include the range of cells to be summed. As an example, assuming that the values are also in A1:A100, the following formula will sum all of the cells in that range that are red
=SUMPRODUCT(--(ColorIndex(A1:A100)=3),A1:A100)

Similarly, to sum the cells with red text, use
=SUMPRODUCT(--(ColorIndex(A1:A100,TRUE)=3),A1:A100)

To sum a separate range based upon coloured cells in A1:A100, use
=SUMPRODUCT(--(ColorIndex(A1:A100)=3),B1:B100)

And to use the colour of a cell as the comparison, like this
=SUMPRODUCT(--(ColorIndex(A1:A100)=ColorIndex(A1)),B1:B100)

Sorting Coloured Cells

In addition, the function can be used to sort a range by its colour. Simply add a 'helper' range next to the original range of coloured cells, and use the ColorIndex function to determine the original cell colour. So, if the cells to be sorted are in A2:A100, add this formula to B2
=ColorIndex(A2)

and copy this formula down to B100. Column B will then contain the set of colorindex values, and the sort is effected on the combined range (and any other appropriate columns/rows), using the newly added 'helper' range as the key range.

If column B already contains key data, then you can use any free column, or insert a new column in front of column B, it can be deleted after the sort.

Custom orders can be managed, but these would have to be defined using the appropriate colorindex, there are no implicit colour names, such as Red or Blue, that can be used.

Filtering Coloured Cells

Filtering coloured cells is very similar to sorting coloured cells. Again, if the cells to be sorted are in A2:A100, add this formula to B2
=ColorIndex(A2)

and copy this formula down to B100. Column B will then contain the set of colorindex values, and the the filter is applied to the helper column.

If column B already contains key data, then you can use any free column, or insert a new column in front of column B, it can be deleted after the sort.

Constraints

There are s couple of major shortcomings with the techniques described here.

The first shortcoming is that change a cell's colour or the text colour does not trigger the Excel calculate event. This is not a shortcoming of the UDF, but of Excel's calculation event, but what this means that any formula that uses the Colorindex UDF to calculate a cell's colour will not update should that cell's colour change. Thus, if a number of cells are being summed, the SUMPRODUCT formula will give the result, but as soon as one or more cells change colour, then the result is incorrect. To overcome this, you need to force a sheet calculate. What I do in applications that use this technique is to create a button(s) to set the colour(s), and within the code attached to the button(s), I do a manual sheet calculate.

The second shortcoming is that this technique at present does not cater for cells that are coloured due to conditional formatting.

Colour Constants

Another 'shortcoming' within Excel is a lack of pre-defined colour constants. If these were available, it would be possible to use a more descriptive formula such as
=IF(ColorIndex(A1)=ciRed,"Warning!","")

One way to achieve this would be to use workbook names, creating names for the colours with a value of the colorindex. For instance, goto Insert>Name>Define...., add the name of ciRed, and a RefersTo value of 3. ciRed will then be available to your formulae.

Code Sample(s)

---

'---------------------------------------------------------------------
' ColorIndex Function
'---------------------------------------------------------------------
' Function:    Returns the colorindex of the supplied range
' Synopsis:    Initially, gets a colorindex value for black and white
'              from the activeworkbook colour palette
'              Then works through each cell in  the supplied range and
'              determines the colorindex, and adds to array
'              Finishes by returning acumulated array
' Variations:  Determines cell colour (interior) or text colour (font)
'              Default is cell colour
' Constraints: Does not count colours set by conditional formatting
'---------------------------------------------------------------------
' Author:      Bob Phillips
'              Additions for ranges suggested by Harlan Grove
'---------------------------------------------------------------------


'---------------------------------------------------------------------
Function ColorIndex(rng As Range, _
                    Optional text As Boolean = False) As Variant
'---------------------------------------------------------------------
Dim cell As Range, row As Range
Dim i As Long, j As Long
Dim iWhite As Long, iBlack As Long
Dim aryColours As Variant

    If rng.Areas.Count > 1 Then
        ColorIndex = CVErr(xlErrValue)
        Exit Function
    End If

    iWhite = WhiteColorindex(rng.Worksheet.Parent)
    iBlack = BlackColorindex(rng.Worksheet.Parent)

    If rng.Cells.Count = 1 Then
        If text Then
            aryColours = DecodeColorIndex(rng, True, iBlack)
        Else
            aryColours = DecodeColorIndex(rng, False, iWhite)
        End If

    Else
        aryColours = rng.Value
        i = 0

        For Each row In rng.Rows
            i = i + 1
            j = 0

            For Each cell In row.Cells
                j = j + 1

                If text Then
                    aryColours(i, j) = _
                      DecodeColorIndex(cell,True,iBlack)
                Else
                    aryColours(i, j) = _
                      DecodeColorIndex(cell,False,iWhite)
                End If

            Next cell

        Next row

    End If

    ColorIndex = aryColours

End Function

'---------------------------------------------------------------------
Private Function WhiteColorindex(oWB As Workbook)
'---------------------------------------------------------------------
Dim iPalette As Long
    WhiteColorindex = 0
    For iPalette = 1 To 56
        If oWB.Colors(iPalette) = &HFFFFFF Then
            WhiteColorindex = iPalette
            Exit Function
        End If
    Next iPalette
End Function

'---------------------------------------------------------------------
Private Function BlackColorindex(oWB As Workbook)
'---------------------------------------------------------------------
Dim iPalette As Long
    BlackColorindex = 0
    For iPalette = 1 To 56
        If oWB.Colors(iPalette) = &H0 Then
            BlackColorindex = iPalette
            Exit Function
        End If
    Next iPalette
End Function

'---------------------------------------------------------------------
Private Function DecodeColorIndex(rng As Range, _
                                  text As Boolean, _
                                  idx As Long)
'---------------------------------------------------------------------
Dim iColor As Long
    If text Then
        iColor = rng.font.ColorIndex
    Else
        iColor = rng.Interior.ColorIndex
    End If
    If iColor < 0 Then
        iColor = idx
    End If
    DecodeColorIndex = iColor
End Function

'---------------------------------------------------------------------
' End of ColorIndex Function
'---------------------------------------------------------------------
				

---

References

The technique described above is best used in conjunction with the SUMPRODUCT function. Details of how this function works, specifically in the way described here, can be found on the page.

Sorting by colour - Special Sort Add-In