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

 

[Peter Davison]

Both seem okay.
I changed to the red colour from the pallet but still didn't work and the trust centre below seems okay

 

[Peter Davison]

Would you be able to test if the code works on your laptop?

 

[Fluff]

The code works quite happily.
Try putting this in the same module & then from Excel press Alt F8 & select the macro & click run. Do you get the message box

Sub check()
   MsgBox "hi"
End Sub

 

[Peter Davison]

Yes I get the message box when I run it.

 

[Fluff]

In that case the function should work, although it will not update automatically.

 

[Peter Davison]

 

[Fluff]

Did you colour the cells before or after entering the formula?

 

[Peter Davison]

I think the problem is the cells it is trying to count are formulas returning a number.
I just put some colours in cells with no formulas and it counted them okay.
Is there something could be added to the code to get round the formularised cells?

 

[Peter Davison]

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

 

[Peter Davison]

I just tested it by entering the colour into a formula cell and it works.
Do you think it won't work because of conditional formatting