Count cells by color in a spreadsheet (cells coloured by conditional formatting)

[Macca69]

Hi there good people!
I'm trying to get excel to count the number of cells in a range (column or row) after the cells have been coloured by conditional formatting rule that stipulates "if the value in the cell is higher than a certain value, the shade the cell in a colour ..." so to speak.

I have tried using VBA and created a formula below and (i think!) saved it in the C Drive / Microsoft / Adins folder ...

Is the script I am using an old one and has the color index changed or do I have to define it ... or what is not working??

I am getting a "0" in the return, which kind of suggests the formual is working, just not recognising the cells or cell colour?

SCRIPT (as follows) and then added as a User Defined function

Function CountCcolor(range_data As Range, criteria AsRange) As Long
    Dim datax AsRange
    Dim xcolor AsLong
xcolor = criteria.Interior.ColorIndex
For Each datax In range_data
    Ifdatax.Interior.ColorIndex = xcolor Then
        CountCcolor= CountCcolor + 1
    End If
Next datax
 
End Function

Hopefully this screen shot will work, but basically the following shows the range first, then the reference cell, then the screen shot shows the result ...

Sick Leave
21.75
0
0
21.75
39.75
14
37 Indicates sick leave hours in excess of the pro-rated 5 days (8 days allowed 1860 Total 3.00 0 <colgroup><col width="64" style="width: 48pt;"> <col width="75" style="width: 56pt; mso-width-source: userset; mso-width-alt: 2742;"> <col width="144" style="width: 108pt; mso-width-source: userset; mso-width-alt: 5266;"> <tbody> </tbody> in the budget tool (departmental average)) =CountCcolor(N7:N9,H238) Sorry, the colur shading keeps copying ... but the return in this instance should have showed "2" as in two cells coloured "blue" Any help greatly appreciated please!! <colgroup><col width="76" style="width: 57pt; mso-width-source: userset; mso-width-alt: 2779;"> <col width="64" style="width: 48pt; mso-width-source: userset; mso-width-alt: 2340;"> <tbody> </tbody>

<colgroup><col width="61" style="width: 46pt; mso-width-source: userset; mso-width-alt: 2230;"> <tbody> </tbody>

 

[Kenneth Hobson]

Re: How do I count cells by color in a spreadsheet (cells coloured by condidtional formatting)

It is simple enough to make your own XLSM to rule out AddIn issues. Or, post your file to some shared site.

 

[Macca69]

Re: How do I count cells by color in a spreadsheet (cells coloured by condidtional formatting)

The issues do not appear to be Addins ... it is issues around counting cells coloured via conditional formatting.

Conditional formatting does not appear to actually change the cell colour in the format .... just an "overlay" hence, the count reference is only counting a "blank coloured" cell whilst the formulae is telling it to count a "coloured cell".

I'm using excel 2013 at work ...

 

[Kenneth Hobson]

Re: How do I count cells by color in a spreadsheet (cells coloured by condidtional formatting)

I know that it works fine in my Win10 Excel 2016. I thought that I had checked it in my Win7 Excel 2013. I will check that tomorrow again as Microsoft says that it is a problem as a UDF in 2013. https://msdn.microsoft.com/en-us/library/office/ff838814.aspx

If that is the case, you will have to use a method like Chip Pearson's posted on his site or use the =Countif method as I explained.

 

[Macca69]

Re: How do I count cells by color in a spreadsheet (cells coloured by condidtional formatting)

OK, so it may be a problem with 2013? and subject to conditional formatting.

Yeah, I have altered the file to count if for each of the ranges, which returns the count of the numbers and left the cells as conditional formatting to change their colour to highlight to management the "issues" to look at ... just a bit of a pain it seems.

Many thanks for responses ...

Not sure I can follow chips codes in looking either and what to write where, so to speak!! (not an excel guru by a long shot!!!)

Cheers!

 

[Kenneth Hobson]

Re: How do I count cells by color in a spreadsheet (cells coloured by condidtional formatting)

Don't be put off by the seemingly complex code that some put out like Chip. He does error checks and adds several optional features typically. While this is hit or miss sometimes, it may meet your goal. Conditional Formatting Colors

Download his BAS file and in VBE, Insert it, which is a Module. You then call the routines as needed. He usually has detailed comments and web instructions showing how to use them.

 

[Macca69]

Re: How do I count cells by color in a spreadsheet (cells coloured by condidtional formatting)

yikes!! that's huge! Do you mean put the entire code on that page into a VBE blank file and save it as a xlam file? Then the function becomes : "functionactivecondition".

Then do I use that function in my spreadsheet where I want the answer to appear and specify my range and the cell colour I want to find (ie as in your earlier eg and the one I reposted where I put in a range A1:A10 and some numbers with conditional format to colour the cells red when 20 or above? Cell C1 was the refernce cell I shaded red ...

Or is it more than that???
Cheers again!!

PS when do you sleep?????? I'm in Australia and at work, so its fine for me!!

 

[Kenneth Hobson]

Re: How do I count cells by color in a spreadsheet (cells coloured by condidtional formatting)

As I said, do not worry about "huge" code like that.

Sorry, I was wrong. I have win7 and Excel 2010 at work. I forgot that we normally stay about 2 versions behind.

I guess the good news is that DisplayFormat worked fine in it as well as I thought. As I said, try it out in an XLSM file first or send your file to me somehow. PM me for my home email if needed. I normally don't do that but this is such a simply thing, anyone could make a simple file and test my code. The reason that is not normally done in most any forum is so that others can learn too. That is what consultants get paid to do.

As for Chip's routine, you would have to add another function to sum up the color or colorindex match counts I believe. Think of his ColorOfCF() as sort of like aRange.DisplayFormat.Interior.Color less aRange.Interior.Color if that governs the interior color fill. Be sure to Compile his code before a run. There is sometimes a catch or two since that code was made a few years ago.