Kay Khan has attended:
Excel Advanced - For Power Users course
Excel Advanced - Formulas & Functions course
Count Cell Colours in Excel.
Hi Team,
Please can I tap onto your expertise as I have a worksheet with many colours and want to know:
1) How to find the colour number
2) How to count the cell colour in worksheet from B10:HT375 for all the different colours.
Appreciate any pointers please.
Thanks
RE: 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
RE: Count Cell Colours in Excel.
Hi Jen,
Thanks for your response.
I have very limited knowledge of VBA, is there any other way of getting the expected result?
Thanks
RE: Count Cell Colours in Excel.
Hi Kay,
1. Finding the Colour Number
To find the colour number of a cell, you can use a simple formula with the GET.CELL function, which is a legacy Excel function. Here's how:
Define a Named Range:
Go to the Formulas tab.
Click on Define Name.
Name it something like CellColor.
In the Refers to box, enter the formula: =GET.CELL(38, Sheet1!A1). Replace Sheet1!A1 with the reference to the cell you want to check.
Use the Named Range:
In a cell where you want to display the colour number, enter the formula: =CellColor.
2. Counting Cells by Colour
To count cells by colour, you can use a combination of conditional formatting and the SUBTOTAL function:
Apply Conditional Formatting:
Select the range B10:HT375.
Go to the Home tab, click on Conditional Formatting, and then New Rule.
Choose Use a formula to determine which cells to format.
Enter a formula that matches the colour criteria (e.g., =CELL("color", B10)=1 for black text).
Set the format to match the cell colour you want to count.
Filter by Colour:
After applying conditional formatting, you can filter the range by colour.
Select the range B10:HT375.
Go to the Data tab, click on Filter.
Use the filter dropdown to filter by the colour you want to count.
Count the Filtered Cells:
Use the SUBTOTAL function to count the visible cells after filtering. For example, =SUBTOTAL(103, B10:HT375).
This method allows you to count cells based on their colour without using VBA.
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
