Countifs for color format

[Doug]

I have columns AA through AK that are conditionally formatted to either a red
fill or a green fill.
How can I enter countifs in a column to tell me how many Green fills there
are in a given row.

So if for a particular row only column AA has a green and AD has a green, it
will tell me "2"

 

[ryguy7272]

Your answer is here:
http://www.cpearson.com/excel/CFColors.htm


This is good too:
http://www.cpearson.com/excel/colors.aspx

HTH,
Ryan---

 

[Doug]

Do I place the Function GetStrippedValue in the sheet view code? I have
placed it in the view code and entered =CountOfCF($AA3:$AK3,2) to show number
of green filled and it shows #name.

I noticed that Pearson 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

The formula I am using is "above average" for columns AA Through AK.

Is this why it is not working?

 

[Gord Dibben]

You need to copy more than one function from Chip's site.

Getstrippedvalue

Activecondition

Countofcf

Place these in a general module in your workbook.

All of this may be unnecessary.

Can't you just count by condition rather than color?

In Z1 enter =AVERAGE(AA3:AK3)

Then in another =COUNTIF($AA3:$AK3">Z1")


Gord Dibben MS Excel MVP

 

[Doug]

After putting the three functions into a module, when I reset the value in
the column it says "Ambiguous name detected: CountOfCF"

 

[Gord Dibben]

So you have decided to go with the color functions rather than the Z1
=AVERAGE(range_to_average)?

The error message "ambiguous name" would indicate you have duplicate
CountOfCF functions or you have named a module CountOfCF.

Do you now have the three functions in a general module?

Did you delete the functions you had placed in in the sheet module?


Gord

 

[Doug]

Thank you for your reply...

I decided that the use of the CountOfCF would serve me better.
=CountOfCF($AA3:$AK3,2)
I have these all the way down the column 3 to 1000. I tried changing the
fill color of RGB to 0,255,0 just to make sure that wasn't a problem. I
checked the modules to make sure there is not one with the name countofcf and
there was not. I tried the three functions in a general module and when it
didn't work I just placed them all in there. In order, I have:
Function ActiveCondition,
Function ColorIndexOfCF,
Function ColorOfCF,
Function GetStrippedValue,
Function CountOfCF,
Function SumByCFColorIndex

 

[Gord Dibben]

Does this mean you are all sorted out or just adding more info to help in
trouble-shooting?

If you are still getting the "ambiguous name" then you haven't looked hard
enough or in the right place(s).

If you desire, please email me the workbook to

gorddibbATshawDOTca change the obvious.


Gord