Count Cells by Colour

[Peter Davison]

Is it possible to count cells by colour in Excel?
I read on a website is wasn't possible unless the code below was added.

Do you feel this is possible?
I am on 365
Thanks for your help

Function CountCellsByColor(data_range As Range, cell_color As Range) As Long
Dim indRefColor As Long
Dim cellCurrent As Range
Dim cntRes As Long
Application.Volatile
cntRes = 0
indRefColor = cell_color.Cells(1, 1).Interior.Color
For Each cellCurrent In data_range
If indRefColor = cellCurrent.Interior.Color
Then
cntRes = cntRes + 1
End If
Next cellCurrent
CountCellsByColor = cntRes
End Function

 

[Fluff]

If the cells are manually coloured then you will need the macro. A formula cannot "see" formatting

 

[Peter Davison]

Does the formula look okay to use?
Do I just add it to the VBA in a module?

 

[Fluff]

Yup it looks ok & it needs to go in a standard module (not a sheet module)

 

[Peter Davison]

I put the code in as per the detail below in a module
I then put the formula in the cell - =countcellsbycolor($I$21:$I$5000,$H$18) - See Pic below but don't get a count.
Any thoughts?

 

[Fluff]

That looks to be in a sheet module, not a standard module.

 

[Peter Davison]

I'm a bit vague on VBA could you advise me how to get it into a standard mode.
Thanks

 

[Fluff]

Just copy the code & paste it into module1

 

[Peter Davison]

It is in Module 1.
I double clicked Module 1 and the code was in there?

 

[Fluff]

Ok, check that macros are enabled & that the cells are exactly the same colour.