Counting Cells with Conditional Formatting

  • Thread starter Thread starter Victor Delta
  • Start date Start date
V

Victor Delta

Sam Harman said:
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?
Click to expand...

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

V
 
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
 
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
Click to expand...

look at the free ASAP Utilities which has such a function
 
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
 
look at the free ASAP Utilities which has such a function
Click to expand...

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
 
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
Click to expand...

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
 
that's it was just beginnings of a solution, also i don't think it would be useful to pursue in this direction
 
Back
Top