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.
Bookmarks