It is not actually possible to do this in Excel without VBA. There is no native functionality whatsoever to analyse anything by colour

The following is a VBA function that can be used on a spreadsheet to count coloured cells by their FONT colour or by their BACKGROUND colour.

The following should be copied and pasted into a standard module within the workbook

For those not used to VBA, press Alt + F11 to open the Visual Basic Editor and then use the Insert menu to Insert>New Module

CODE

Function CountColour(Rng As Range, ColourMatch As Integer, BackgroundOrFont As String)
'Rng is the set of cells to be checked
'ColurMatch is the Color INDEX of the colour being tested for
'BackgroundOrFont requires an "F" or "B" to indicate whether to test Font or Background colour

Dim c As Range, TempStore As Long

TempStore = 0

Select Case BackgroundOrFont
    Case "B"
        For Each c In Rng
            If c.Interior.ColorIndex = ColourMatch Then
                TempStore = TempStore + 1
            End If
        Next
    Case "F"
        For Each c In Rng
            If c.Font.ColorIndex = ColourMatch Then
                TempStore = TempStore + 1
            End If
        Next
    Case Else
        CountColour = "Choose F or B only"
        Exit Function
End Select

CountColour = TempStore

End Function

You can use this function on a spreadsheet by entering

=CountColour(A1:A100,3,"B")
This will count the number of cells in A1:A100 which have a Background colour of Red (3)

=CountColour(B1:B500,5,"F")
This will count the number of cells in B1:B500 that have a Font colour of Blue (5)

The caveat to this is that it will not work if the colouring of the cell is as a result of Conditional Formatting - this requires a lot of extra coding and for the sake of ease, I have not included that here

A few common ColorIndexes

1 Black
2 White
3 Red
4 Green
5 Blue (standard)
6 Yellow
7 Pink
8 Turquoise
15 Light Grey

To obtain a list of all color Indexes, create a new worksheet and run the following code:

CODE

Sub List_Color_Indexes()
For i = 1 To 56
Activesheet.Cells(i, 1).Interior.ColorIndex = i
Next i
End Sub

The ColorIndex for the colour shown will be the ROW number

Back to VBA Visual Basic for Applications (Microsoft) FAQ Index
Back to VBA Visual Basic for Applications (Microsoft) Forum

My Archive

My FAQ Archive

Come Join Us!

Posting Guidelines

Jobs

VBA Visual Basic for Applications (Microsoft) FAQ

Excel How To

CODE

CODE

My Archive

Join Tek-Tips® Today!

Join Tek-Tips^® Today!