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]

The colours are from a conditional formula, do you think that makes a difference

Yes that's why I originally said

If the cells are manually coloured

If they are as a result of conditional formatting then you can just use the the CF rules to calculate how many cells are coloured.

 

[Peter Davison]

Sorry if I have misled you.
I'm not too familiar with this type of counting.
Where in the conditional formatting does it allow me to count?

I watched a video after your message and the person said use - =countconditioncolorcells(range,colorcell)
But when I type that in I don't have that formula option.

So any further help would be appreciated.
Thank you

 

[Fluff]

You just use a formula in the sheet that counts how many cells match the conditional formatting rule.

 

[Peter Davison]

So would that be a countif?
I'm trying to find out the number of cells in I21:I5000 that don't equal the same cells in H21:h5000
I am so sorry to be a pain and you've been so patient and helpful.

It would be great in a future release of Excel if that had a simple count color cells.

 

[Fluff]

It would be great in a future release of Excel if that had a simple count color cells.

I would have to disagree, colour is not data & should not be used as such.
Try

=SUM(--(I2:I5000<>H2:H5000))

 

[Peter Davison]

I got there and thanks all to you.
I really appreciate your time and help.
Maybe one day I will get just 10% of your knowledge.
Thanks again.

 

[Fluff]

You're welcome & thanks for the feedback.