calculate numbers based on font color?

C

Costa

Is there a formula to calculate numbers based on their font color?

For example, say cells B1 to B10 all have numbers in them. Half th
numbers have a font color of Red and the other half Green.

I would like to total all the red numbers with a formula.
Is there a way to do this?

Thank Yo
 
R

Richard Buttrey

Is there a formula to calculate numbers based on their font color?

For example, say cells B1 to B10 all have numbers in them. Half the
numbers have a font color of Red and the other half Green.

I would like to total all the red numbers with a formula.
Is there a way to do this?

Thank You
Click to expand...

I don't know of a standard Excel Function, but you could write a
userdefined function in VBA to do this. One way would be:

Public Function SumRedFont(Myrange As Range)
Dim x As Integer, y As Integer
Dim MySum As Double

x = Myrange.Cells.Count

For y = 1 To x
If Myrange.Cells(y, 1).Font.ColorIndex = 3 Then
MySum = MySum + Myrange.Cells(y, 1)
End If
Next

SumRedFont = MySum

End Function


Then in say C1 you would enter the formula

=SumRedFont(B1:B10)

This assumes all cells are in one column, i.e. B. You'd need to modify
it slightly if the range was two dimensional.


HTH



__
Richard Buttrey
Grappenhall, Cheshire, UK
__________________________
 
Top