Counting cells via font color

Hi James

I thought I had posted this for you a few days ago, but it didn't come through. Anyway here it is again.

1 Select Alt+F11. go to Insert>Module, paste in the code below:

Function CountCol(SumRange As Range) As Integer
Dim i As Integer
Dim Cell As Range
Set SumRange = SumRange.SpecialCells(xlCellTypeAllFormatConditions)
For Each Cell In SumRange
If Cell > Cell.FormatConditions(1).Formula1 Then
i = 1 + i
End If
Next Cell
CountCol = i

End Function

2. Push Alt+Q. Save

Now put: =CountCol(B1:C100) in any cell.

Where B1:C100 are the cells containing your Conditional formats.

If you have the Format condition set as:
"Cell value is"-"Greater than"-20

"Cell.FormatConditions(1).Formula1" will return the number 20 and compare this with each cell in range B1:C100. If the cell number is over 20 then it will coun the cell.

Any good ?

OzGrid Business Applications

This can be achieved with a simple worksheet formula :
=COUNTIF(B1:B100,">20")
Celia

Celia, this would count cells without Conditional Formatting as well though.

Dave
OzGrid Business Applications

, this would count cells without Conditional Formatting as well though. Dave

I don't understand the point of your comment. The original question requires the count of cells over a certain value and states that all such cells have a red font. It is only necessary, therefore, to count the cells by value(which is simple) rather than by font colour(which is not simple).
(Please note also that the question doesn't mention that the red format was achieved by conditional formatting.)
Celia

Image

, this would count cells without Conditional Formatting as well though. Dave

Dave: Are you posting an image of your business now in addition to the hyperlink to it with your responses? Or is it my browser that is gone wild?

Aladin

Re: Image

Yes! I am. No your browser hasn't gone wild.

OzGrid Business Applications

Celia

>the data if over a certain value will have a red font

I assume this is done via Conditional Formatting.

Dave
OzGrid Business Applications

I don't understand the point of your comment. The original question requires the count of cells over a certain value and states that all such cells have a red font. It is only necessary, therefore, to count the cells by value(which is simple) rather than by font colour(which is not simple). (Please note also that the question doesn't mention that the red format was achieved by conditional formatting.)

>the data if over a certain value will have a red font I assume this is done via Conditional Formatting. Dave

Whether it is or is not done with conditional formatting is not the point. Why use VBA to count the cells when it can be done with a simple worksheet formula?
Celia

Because if using conditionan formating the value at which it turns red isn't always the same. Imagine two toxic of two different type (A and B), each with it's own toxicity level (1 and 2). Then it could be that that when toxic is A and level is more than 1, make the font red. And if Toxic is B and more than 2, make it red and so on and so forth with other varying criteria. If would be more efficient to count by color than to use a formula with multiple criteria. Please feel free to barage me with insults if I am wrong. ; )

Cosmo
You are creating a very different scenario from the original question. The solution of a simple worksheet formula is based upon having only one criterion as stated in the original request, which was :- .
"I have twelve columns and in these columns, the data if over a certain value will have a red font. How, if possible, can I have a formula that would only count the number of cells that have the red font?"
Celia

I know, but the question part is "How, if possible, can I have a formula that would only count the number of cells that have the red font?".
This part - "I have twelve columns and in these columns, the data if over a certain value will have a red font." is just a statement. Of course, we could argue semantics all day and say that wasn't the intent, but I was trying to explain why a more general way to do it would prove more useful than just a particular case as the question part might or might not imply...