calculate numbers based on font color?
 

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


--
Costa
------------------------------------------------------------------------
Costa's Profile: http://www.excelforum.com/member.php...o&userid=26980
View this thread: http://www.excelforum.com/showthread...hreadid=401912

On Mon, 5 Sep 2005 07:43:20 -0500, Costa
wrote:


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

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
__________________________