sum of the same formated numbers

[still learning]

Hi
I have the following code in a module

Function ColorSum(ColorCell As Range, SumRange As Range) As Variant
Dim Cell As Range
For Each Cell In SumRange
If Cell.Font.ColorIndex = ColorCell.Font.ColorIndex Then
ColorSum = Cell + ColorSum
End If
Next
ColorSum = ColorSum
End Function

In a cell I have

=colorSum(BG1, cashweek4)

Where BG1 is formatted Black, and “cashweek4” is the range that I enter numbers.<?xml:namespace prefix = o /><o></o>
<o></o>
If I enter a number in black (in the range) the formula works fine. (it adds all the black numbers). If I change the entered number to a different color, the formula still thinks it’s black. If I start with a different color and then change it to black, the formula doesn’t recognize it. I have to retype the formula in the cell to get the proper solution. I tried to use F9, but it doesn’t help.<o></o>
What Am I doing wrong?


Mike <o></o>

<o></o>

 

[VoG]

Try

Function ColorSum(ColorCell As Range, SumRange As Range) As Variant
Application.Volatile
Dim Cell As Range
For Each Cell In SumRange
If Cell.Font.ColorIndex = ColorCell.Font.ColorIndex Then
ColorSum = Cell + ColorSum
End If
Next
ColorSum = ColorSum
End Function

You will still need to press F9.

 

[still learning]

Hi Pete
Thanks. Since I have to press F9, I'll write an event macto for it.



Can't make out the name on the bottle.
Mike