Count Cell Colours in Excel.

Hi Kay,

Thank you for the forum question.

Here's how you can achieve both tasks in Excel:

1. Finding the Colour Number
To find the colour number of a cell, you can use a small VBA (Visual Basic for Applications) script:

Press Alt + F11 to open the VBA editor.
Insert a new module by clicking Insert > Module.
Copy and paste the following code into the module:
Function GetColorNumber(rng As Range) As Integer
GetColorNumber = rng.Interior.Color
End Function
Close the VBA editor.
In your worksheet, use the function =GetColorNumber(A1) (replace A1 with the cell you want to check) to get the colour number.
2. Counting Cells by Colour
To count cells by colour in a specific range, you can use another VBA script:

Open the VBA editor again (Alt + F11).
Insert a new module (Insert > Module).
Copy and paste the following code:
Function CountColorCells(rng As Range, color As Range) As Long
Dim cell As Range
Dim count As Long
Application.Volatile
For Each cell In rng
If cell.Interior.Color = color.Interior.Color Then
count = count + 1
End If
Next cell
CountColorCells = count
End Function
Close the VBA editor.
In your worksheet, use the function =CountColorCells(B10:HT375, A1) (replace A1 with a cell that has the colour you want to count) to count the cells of that colour in the range B10:HT375.
Example Usage
To find the colour number of cell B10, use =GetColorNumber(B10).
To count all cells in the range B10:HT375 that have the same colour as cell A1, use =CountColorCells(B10:HT375, A1).



Kind regards

Jens Bonde
Microsoft Office Specialist Trainer

Tel: 0207 987 3777
STL - https://www.stl-training.co.uk
98%+ recommend us

London's leader with UK wide delivery in Microsoft Office training and management training to global brands, FTSE 100, SME's and the public sector