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
[color red]Alt + F11[/color] to open the Visual Basic Editor and then use the [color red]Insert[/color] menu to [color red]
Insert>New Module[/color]
Code:
Function CountColour(Rng As Range, ColourMatch As Integer, BackgroundOrFont As String)
[color green]'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 [b]F[/b]ont or [b]B[/b]ackground colour[/color]
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")
[color blue]This will count the number of cells in A1:A100 which have a Background colour of Red (3)[/color]
=CountColour(B1:B500,5,"F")
[color blue]This will count the number of cells in B1:B500 that have a Font colour of Blue (5) [/color]
[color red]
The caveat to this is that it will not work if the colouring of the cell is as a result of [/color][color green]
Conditional Formatting[/color][color red]
- this requires a lot of extra coding and for the sake of ease, I have not included that here[/color]
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
