Counting number of cells with certain conditional formatting

[TOTCOM11]

I am trying to store the number of times a certain condtional format happens into a cell. For example, let's say I'm looking at cells A1 through A10 and B1 through B10. I want to look at all of these cells and count the number of times there are bold formatting, and store that number in A12. And I also want to find the number of times there is formatting with yellow fill color and store that number in A13. Does anyone know how I can do this? If you need any clarification of what I'm trying to accomplish, let me know.

Thanks,
TOTCOM11

 

[SkipVought]

TOTCOM11,

Copy your conditional format experssion and paste it as a FORMUAL into a cell.

Modify to meet your counting requirements.

Skip,

_{[red]Sign above the facsimile apparatus at the music publisher:[/red]
If it ain't baroque...
Don't FAX it!}

 

[TOTCOM11]

I'm sorry....I am not sure I follow...

 

[SkipVought]

The same CRITERIA that you use for the conditional formatting can be used to, for instance, COUNT.

Skip,

_{[red]Sign above the facsimile apparatus at the music publisher:[/red]
If it ain't baroque...
Don't FAX it!}

 

[AnotherHiggins]

You can't count formats of other cells without VBA.

BUT, you said that you have conditional formatting causing these cells to be bold or yellow. Well, just restate whatever formulas you have in the conditional formatting.

[tt]_____
[blue]-John[/blue][/tt]

"Patriotism is your conviction that this country is superior to all other countries because you were born in it."
[tab]-George Bernard Shaw

Help us help you. Please read FAQ181-2886 before posting.

 

[TOTCOM11]

The thing is that depending on what row the data is in, the formula changes that makes each cell change yellow. That's why I just want to be able to count the total number of yellow cells, or bold cells. Is there any way in VBA that I can do that and store it in a cell?

 

[GlennUK]

Hi TOTCOM11,
I doubt that the formula changes, but unless you post the formula the makes each cell yellow ( in Conditional Formatting ), we'll never be able to tell. So post the formula will you.

Cheers, Glenn.

Did you hear about the literalist show-jumper? He broke his nose jumping against the clock.

 

[TOTCOM11]

Alright, I have 2 rows of 10 cells that I am looking at: Rows 3 and 4 using columns D through M. For checking yellow fill I am using the formula =$D$15 for those cells in column D, =$E$15 for those cells in column E, =$F$15 for those cells in column F...etc... In the second row only (row 4) I am checking to see if the number in each column is equal to the number in the row directly above it. So for example if D4<>D3, then I am formatting the text in this cell to be bold. If E4<>E3, then the text is to be bold...and so on and so forth until I get to column M. Does that help anything? Let me know if you need any more explaning.

Thanks,
TOTCOM11

 

[GlennUK]

To count yellows:

=SUM(IF(D3:M4=D15:M15,1,0))

entered using Ctrl-Shift-Enter instead of Enter.

To count bolds:

=SUM(IF(D4:M4<>D3:M3,1,0))

also entered using Ctrl-Shift-Enter instead of Enter.

This counting may not be exactly what you expect, as Conditional Formatting stops once a condition is met, so if a cell is yellow it won't show as bold too, whereas my sums work for all cases.

Cheers, Glenn.

Did you hear about the literalist show-jumper? He broke his nose jumping against the clock.

 

[TOTCOM11]

Just curious, what does CTRL-SHIFT-ENTER do that Enter doesn't do?

 

[AnotherHiggins]

It enters the formula as an Array Formula. (Have a look in Excel's help file for more info.)

[tt]_____
[blue]-John[/blue][/tt]


Help us help you. Please read FAQ181-2886 before posting.