Using Conditional Formatting and ColorFunction?

[SDJ98]

Hi All,

I have a spreadsheet with a bunch of different metrics (in the columns). My conditional formatting is set to shade the cell if they meet a certain threshold, but some are things like letter grades and others are numbers. My plan was to then count the number of shaded cells at the end. I've got the colorfunction working, however it will not work with conditional formatting. My understanding is that conditional formatting doesn't change the actual cell color.

Is there any way to modify the two or perhaps add in another function. My only other option is to sum about 25 columns x 150 rows manually (looking at the shaded cells and just counting in my head), so any help is GREATLY appreciated.

Thanks.

 

[Andrew Poulsom]

Look here:

http://www.cpearson.com/Excel/CFColors.htm

 

[VoG]

This may help you http://www.cpearson.com/excel/CFColors.htm

 

[SDJ98]

How would I go about setting any of those formulas to COUNT the number of cells that are conditionally formatted to return a certain shading?

 

[VoG]

Try the CountOfCF function from the link.

 

[SDJ98]

I set my formula to

=countofcf(B8:AO8,1)

where B8:A08 is my range of data I want to count the number of shaded cells in. The current formula returns 9, which is neither the number of shaded cells, nor the number of cells that are set to have a conditional formatting (whether its currently 'triggered' or not).

Any thoughts?

 

[SDJ98]

last ditch bump before I start counting these cells by hand...thanks

I made sure every conditional format referrences an actual cell, not a formula.

 

[SDJ98]

still hoping to get some resolution on this. if I use the formula =activecondition(A1)

it should tell me if the cell is returning a conditional formot of 1, 2, 3 or 0, with zero meaning no condition applies correct? I ask because on some of the cells where there is conditional formatting it returns zero, but on others where it doesn't have formatting, it returns 1.

I just pasted the code from Chip Pearson into a module, FWIW

 

[GlennUK]

And Chip says:

NOTE: ActiveCondition may result in an inaccurate result if the following are true:

You are calling ActiveCondtion from a worksheet cell, AND
The cell passed to ActiveCondtion uses a "Formula Is" rather than
"Cell Value Is" condition, AND
The formula used in the condition formula contains relative addresses

... is that the case?

 

[SDJ98]

And Chip says:... is that the case?

It's possible, but only if I'm not understanding what he's trying to say.


My spreadsheet is set up like


................Metric 1 .......Metric 2 .........Metric 3
Office 1 .........A+ .............80% ..........10,000
Office 2 .........B- ..............16% ...........7,500
Office 3 ..........C ...............70% ..........8,800

where each metric is a different kind of information. They're all pulling from getpivotdata functions or vlookups. Each cell is set to a conditional format. The conditional formats all follow the same pattern of "Cell Value is", then some version of "greater than" then "=$A$1" where the cell value is just a percent, number, etc that I set based on what I want to highlight.

None of the conditional formats reference formulas, so I'm not sure what the problem is.