Counting Cells with Conditional Formatting

[Victor Delta]

So I think it should be easy but it isnt.......

I have a number of cells in a row that are conditionally formatted and
sum that are manually formatted...all have a background colour (either
red, green or yellow) and all have the text colour of red...

What I would like to do is add up how many cells have the text colour
of red and sum this at the end of the row......

Is that possible?

Have a look at this http://www.ozgrid.com/VBA/sum-count-cells-by-color.htm

V

 

[Jim Cone]

Depends on whether the font color is created by conditional formatting or not.
If it is CF you will have to count the number of CF formulas that evaluate to true.
A nice learning experience.
--
Jim Cone
Portland, Oregon USA
http://excelusergroup.org/media/
(Formats & Styles xl add-in: lists/removes unused styles & number formats) - free



"Sam Harman" <[email protected]>
wrote in message
news:[email protected]...

 

[Sam Harman]

So I think it should be easy but it isnt.......

I have a number of cells in a row that are conditionally formatted and
sum that are manually formatted...all have a background colour (either
red, green or yellow) and all have the text colour of red...

What I would like to do is add up how many cells have the text colour
of red and sum this at the end of the row......

Is that possible?

Any help appreciated

Thanks

Sam

 

[Zaidy036]

So I think it should be easy but it isnt.......

I have a number of cells in a row that are conditionally formatted and
sum that are manually formatted...all have a background colour (either
red, green or yellow) and all have the text colour of red...

What I would like to do is add up how many cells have the text colour
of red and sum this at the end of the row......

Is that possible?

Any help appreciated

Thanks

Sam

look at the free ASAP Utilities which has such a function

 

[isabelle]

hi Sam,

a track start..

Sub test()
Dim f1 As String, f2 As String, op As String
Dim n As Integer, c As Range, rng As Range, fcs
Set rng = Range("A1:A10")
On Error Resume Next
For Each c In rng
For Each fcs In c.FormatConditions
With fcs
f1 = .Formula1
f2 = .Formula2
op = .Operator
End With
Select Case fcs.Type
Case xlCellValue
Select Case op
Case xlBetween: If c > CDbl(f1) And c < CDbl(f2) Then n = n + 1
Case xlNotBetween: If c < CDbl(f1) And c > CDbl(f2) Then n = n + 1
Case xlEqual: If c = CDbl(f1) Then n = n + 1
Case xlGreater: If c > CDbl(f1) Then n = n + 1
Case xlGreaterEqual: If c >= CDbl(f1) Then n = n + 1
Case xlLess: If c < CDbl(f1) Then n = n + 1
Case xlLessEqual: If c <= CDbl(f1) Then n = n + 1
Case xlNotEqual: If c <> CDbl(f1) Then n = n + 1
End Select
Case xlExpression: If Evaluate(f1) Then n = n + 1
End Select
Next
Next
MsgBox n
End Sub

 

[Sam Harman]

look at the free ASAP Utilities which has such a function

Hi and thanks for your reply.

I have the ASAP utilities but cannot find that particular function. It
can count cells that are filled but cannot count cells that are
conditionally formatted....unless of course I have missed it !!

If so can you pleas point me in the right direction?

Thanks

Sam

 

[Sam Harman]

hi Sam,

a track start..

Sub test()
Dim f1 As String, f2 As String, op As String
Dim n As Integer, c As Range, rng As Range, fcs
Set rng = Range("A1:A10")
On Error Resume Next
For Each c In rng
For Each fcs In c.FormatConditions
With fcs
f1 = .Formula1
f2 = .Formula2
op = .Operator
End With
Select Case fcs.Type
Case xlCellValue
Select Case op
Case xlBetween: If c > CDbl(f1) And c < CDbl(f2) Then n = n + 1
Case xlNotBetween: If c < CDbl(f1) And c > CDbl(f2) Then n = n + 1
Case xlEqual: If c = CDbl(f1) Then n = n + 1
Case xlGreater: If c > CDbl(f1) Then n = n + 1
Case xlGreaterEqual: If c >= CDbl(f1) Then n = n + 1
Case xlLess: If c < CDbl(f1) Then n = n + 1
Case xlLessEqual: If c <= CDbl(f1) Then n = n + 1
Case xlNotEqual: If c <> CDbl(f1) Then n = n + 1
End Select
Case xlExpression: If Evaluate(f1) Then n = n + 1
End Select
Next
Next
MsgBox n
End Sub

Thank you very much for your response isabelle, but unfortunately as a
relative novice I am not sure what I should do with the formula
above...

Any assistance for an idiot would be appreciated

Thanks

Sam

 

[isabelle]

that's it was just beginnings of a solution, also i don't think it would be useful to pursue in this direction