SUMIF by Text colour

[TimS]

Is it possible to SUMIF by text colour. Hurray if you can (and how do you do it?) Bah Humbug if you can't.


[ This Message was edited by: TimS on 2002-04-17 21:05 ]

[Jay Petrulis]

Hi,

I have posted a UDF which is *not* working and a subroutine which is essentially identical and does work.

I have no idea why the UDF isn't working. Possibly others will have an answer, but this might get you started.

---------------------
Function SumColor(Rng As Range, textcolor As Integer)
Dim UsedCell As Range, MySum As Double

For Each UsedCell In Rng
If IsNumeric(UsedCell.Value) Then
If UsedCell.Font.ColorIndex = textcolor Then
MySum = MySum + UsedCell.Value
End If
End If
Next UsedCell

End Function

Sub Sumclr()
textcolor = 3
For Each Cell In Selection
If IsNumeric(Cell.Value) Then
If Cell.Font.ColorIndex = textcolor Then
MySum = MySum + Cell.Value
End If
End If
Next Cell
MsgBox MySum
End Sub
----------------------------

Regards,
Jay

[Ivan F Moala]

On 2002-04-17 21:24, Jay Petrulis wrote:
Hi,

I have posted a UDF which is *not* working and a subroutine which is essentially identical and does work.

I have no idea why the UDF isn't working. Possibly others will have an answer, but this might get you started.

---------------------
Function SumColor(Rng As Range, textcolor As Integer)
Dim UsedCell As Range, MySum As Double

For Each UsedCell In Rng
If IsNumeric(UsedCell.Value) Then
If UsedCell.Font.ColorIndex = textcolor Then
MySum = MySum + UsedCell.Value
End If
End If
Next UsedCell

End Function

Sub Sumclr()
textcolor = 3
For Each Cell In Selection
If IsNumeric(Cell.Value) Then
If Cell.Font.ColorIndex = textcolor Then
MySum = MySum + Cell.Value
End If
End If
Next Cell
MsgBox MySum
End Sub
----------------------------

Regards,
Jay

Jay the function needs to be finished off with

SumColor = MySum

[Jay Petrulis]

Hi Ivan,

DOH! Thanks for that. I think that is rule number one in creating UDFs, but somehow I missed it.

Thanks,
Jay

[Jack in the UK]

my old pal Ivan never going to give you less that his best and thats whats here that missing bit.

but if you want some goodies i suggest another pal i chat and have a drink with over the email, Dave Hawley check ozgrid.com, there is a download and sorce code that does just this.

PLEASE can i request you post the detals here so all can see the wonderful work add your comments as well..

this will help...

PS if you miss a nights sleep once you surf Dave s site .. not my fault,,,