Count Conditional Formatted cells [Closed]

Report
-
venkat1926
Posts
1862
Registration date
Sunday June 14, 2009
Status
Contributor
Last seen
July 30, 2015
-
Hello,

I am trying to count cells that have a conditional format already applied to change their interior color to green (4). I have written a function but the color returned from the cell is still white rather than the newly assigned color of green which has been applied through condittional formatting within the cell.

Can anyone help.

4 replies

Posts
1862
Registration date
Sunday June 14, 2009
Status
Contributor
Last seen
July 30, 2015
789
not cler.
do you want to count the cells where conditional formatting cells are there whether cells turned green or not
or
do you want to count only those cells which have turned green?
I'm not the original poster, but I'm trying to do the same thing, or something similar. I want to count the cells that have turned green due to conditional formatting. I appreciate any help you can offer.
Posts
1862
Registration date
Sunday June 14, 2009
Status
Contributor
Last seen
July 30, 2015
789
kevrhon


perhaps in excel 2007 it is easier.

In other versions
you count the condition being satisfied in that range

see the macro below and the comments there in . you have to modify the condition to suit your conditions.



Sub test()
Dim j As Long, r As Range, c As Range
'suppose the conditional format of green is "the cell value is >10"
Set r = Range("A1:a25")
For Each c In r
If c > 10 Then j = j + 1
Next c
MsgBox "the no. of cells turned green due to being >10 is    " & j

End Sub
I should have been more explicit. I have a spreadsheet set up to compare the accuracy of the picks of NFL handicappers picking against the spread. Under each handicapper, I have four columns, one for the favorite team, another for their projected score, a third for the underdog and the fourth for their score. The conditional formatting highlights in yellow the team each handicapper is projecting as the winner, based on their projected score including the point spread added to the underdog's score.

After the games are played, I enter the winning team's name in yet another column. The conditional formatting then highlights in green the picks made by the handicapper which are correct. As there are 16 games most weeks, and two teams in each game, there are 32 conditionally formatted cells for each handicapper each week - too many separate operations to put into a formula in a single cell. I was hoping to find some way to count just the green highlighted cells within the array of the two columns and 16 rows for each handicapper at the end of the week.

I've found a solution that involves totaling each column in a separate cell, then adding those two cells into my actual target cell. It works, but it's clunky. I'm thinking there is surely a more elegant solution.
Posts
1862
Registration date
Sunday June 14, 2009
Status
Contributor
Last seen
July 30, 2015
789
suggest open a new thread,post very small extract of your sheet(S) and explain what you want withe two or three examples.