UDF to Count Conditionally formatted cells by colour

Hi All,

New guy here, I have tried searching through the threads but have been unable to find anything that will help me so I apologise if this has been covered before.

I am using Excel 2010 if that helps.

I am trying to build a User Defined Function that will count the number of cells which have been formatted a particular colour in a range through conditional formatting.

I absolutley do need a UDF that counts based on colours, counting by criteria that extablished the CF is not possible as not all cells within the range will have the same conditional formatting rules.

I have scoured the web for solutions but have not been able to make them work (I am sure this is because of something I am doing).
I have tried Chip Pearsons example at http://www.cpearson.com/excel/CFColors.htm but alas the code is not working.

The problem I am having when trying to use Chip's code seems to be somewhere in the 'ActiveCondition' function. I am not getting an error messages in VBA however when I try to use the function in a sheet it returns '#Value!'.

I have attached a copy of a test workbook that contains the UDF's I have tried to build. I would really appreciate it if anyone could take a look through it and tell me where I am going wrong - particularily with the ActiveCondition function.

Thanks in advance!

Pl see attached file with UDF

CountColor for counting cells with given Colour Index in the given range.

CountAllColors for counting all cells with Colour Index in the given range.

i do not see the countallcolors formula? Is this a formula or a condistional formatting?

You Pl check once again.
It is used I3 cell.

Originally Posted by vlady

Is your "macro" enabled??

btw why not use the formula in your conditional formatting to count the colored cells (coloured using conditional formatting)...

Hi Vlady,

Yes all Macros are enabled.

As I mentioned in the post at the start of the thread, I cannot use the formula in the conditional formatting to count the cells as the finished table will have many different conditoins in different cells. Therefore there will not be one particular formula that I can count.

bump - anyone have any ideas?

Hi Mike,

Thanks for the suggestion, however unfortunatly it wont work in this instance.
If you read my earlier post - It is not possible for me to count based on the condition as the columns and rows i want to apply this too will contain different conditions.

For example, in column a, row 1 might have a condition that will turn it red if the cell = "stop", but row 2 in that same column will have a condition that turns a cell red if the cell is equal to the number 2 or perhaps if it is between 3 and 6 etc, and so on.

So I really need something that can count based the colour of the conditionally formatted cell.

Hi Mike,

I appreciate you taking the time to consider this issue.

Unfortunatly, the data I am trying to analyse is a table that consists of 150+ unique, lets call them organisations, who have been assesed against 30+ different categories. As such the cells, depending on the column, can be numbers, percentages, True/False. The table mesaures the organisations performance against the category, returning a red, orange or green cell colour. Each of those columns has a differerent set of a conditional formatting rules depending on the category against which the organisation is being assessed. The goal is to be able to tell how many red cells there are in each column and row.

Yes it is possible to develop a complicated and long counting formula but this is exactly what I am trying to avoid as I will have to do the same sort of analysis against different tables with different rules later on in the process.

I want to simplify the process and not have to work out the counting formula for each row and column if that is at all possible.

The Counting conditional formatting colours UDFs that have been suggested by Chip Pearson http://www.cpearson.com/excel/CFColors.htm seem to do what I want however I cant get it to function properly in the workbook I have attached at the start of the post. What I would really like is for someone to help me identify what is wrong with the the three UDFs I copied from Chips website in Module1 of my workbook, namely:
* GetStrippedValue
* ActiveCondition
and
* CountOfCF

I believe the issue is in the Active Condition UDF but I cant isolate what it is.

There is no doubt that in many (most) cases, counting based on a formula rather than conditionally formatting is simpler and quicker but in my case being able to spend the time now and set this up would make the work I am doing on this table and future work on simlar but different tables with different formulas and conditions much much simpler and easier in the long run.

Hi did you get an answer to this issue, as I've been looking for one for the past 24hrs with no success.

Originally Posted by ozzy_q

Hi Mike,

I appreciate you taking the time to consider this issue.

Unfortunatly, the data I am trying to analyse is a table that consists of 150+ unique, lets call them organisations, who have been assesed against 30+ different categories. As such the cells, depending on the column, can be numbers, percentages, True/False. The table mesaures the organisations performance against the category, returning a red, orange or green cell colour. Each of those columns has a differerent set of a conditional formatting rules depending on the category against which the organisation is being assessed. The goal is to be able to tell how many red cells there are in each column and row.

Yes it is possible to develop a complicated and long counting formula but this is exactly what I am trying to avoid as I will have to do the same sort of analysis against different tables with different rules later on in the process.

I want to simplify the process and not have to work out the counting formula for each row and column if that is at all possible.

The Counting conditional formatting colours UDFs that have been suggested by Chip Pearson http://www.cpearson.com/excel/CFColors.htm seem to do what I want however I cant get it to function properly in the workbook I have attached at the start of the post. What I would really like is for someone to help me identify what is wrong with the the three UDFs I copied from Chips website in Module1 of my workbook, namely:
* GetStrippedValue
* ActiveCondition
and
* CountOfCF

I believe the issue is in the Active Condition UDF but I cant isolate what it is.

There is no doubt that in many (most) cases, counting based on a formula rather than conditionally formatting is simpler and quicker but in my case being able to spend the time now and set this up would make the work I am doing on this table and future work on simlar but different tables with different formulas and conditions much much simpler and easier in the long run.