Conditional format, count cells, formula is , cannot use Cpearson's Active Condition fcn

seagreen

Board Regular
Joined
Feb 7, 2009
Messages
71
Hello,
I've been searching to see if the question I'm about to ask has been solved but so far haven't found an answer. I thought I could use this:
http://www.cpearson.com/excel/colors.aspx

He 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

But, I'm afraid I have a "formula is" type conditional format so the background fill is not "read" by the function. My conditional format rule is like this:
=MATCH(E2,myNamedRange,0) I have a long column with corresponding rules (the E2 become En where n is the row number). I would like to count how many cells are colored. there is only one condition. It's either colored or it's not. I have tried using =COUNTIF with the formula I use in the conditional format rule but haven't any luck.

Can this problem be solved?

Thanks!
 

Aladin Akyurek

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,138
If

=MATCH(E2,myNamedRange,0)

is the formula in CF, the following would yield a count:

=SUMPRODUCT(ISNUMBER(MATCH(E2,myNamedRange,0))+0)
 

seagreen

Board Regular
Joined
Feb 7, 2009
Messages
71
Aladin @ The Hague: Brilliant! I changed "E2" to E2:E7002 so I could count for the full range and it worked perfectly.
Thanks!!
 

Forum statistics

Threads
1,081,690
Messages
5,360,614
Members
400,592
Latest member
badgergurl

Some videos you may like

This Week's Hot Topics

  • VBA (Userform)
    Hi All, I just would like to know why my code isn't working. Here is my VBA code: [CODE=vba]Private Sub OKButton_Click() Dim i As Integer...
  • List box that changes fill color
    Hello, I have gone through so many pages trying to figure this out. I have a 2020 calendar that depending on the day needs to have a certain...
  • Remove duplicates and retain one. Cross-linked cases
    Hi all I ran out of google keywords to use and still couldn't find a reference how to achieve the results of a single count. It would be great if...
  • VBA Copy and Paste With Duplicates
    Hello All, I'm in need of some input. My VBA skills are sub-par at best. I've assembled this code from basic research and it works but is...
  • Macro
    is it possible for a macro to run if the active cell value is different to the value above it
  • IF DATE and TIME
    I currently use this to check if date has passed but i also need to set a time on it too. Is it possible? [CODE=vba]=IF(B:B>TODAY(),"Not...
Top