SumByCellColor
 

Anyone an idea why my function does not work... it seems to unload...

Public Function SumByCellColor(Cellrange As range, Cellcolor As Single) As Single
Application.Volatile
n = 0
For Each RangeCell In Cellrange
If RangeCell.Interior.ColorIndex = Cellcolor Then
If IsNumeric(RangeCell.Value) Then n = n + RangeCell.Value
End If
Next SumByCellColor = n
End Function

Thx in advance

SumByCellColor
 

"SIGE" skrev i melding
om...
Anyone an idea why my function does not work... it seems to unload...

Public Function SumByCellColor(Cellrange As range, Cellcolor As Single) As
Single
Application.Volatile
n = 0
For Each RangeCell In Cellrange

n and RangeCell are not declared

Next SumByCellColor = n

This can't do any good.
Also, coloring doesn't trig a calculation, you need an F9 or similar to make
a recalculation happen.

HTH. Best wishes Harald

SumByCellColor
 

This works for me

Public Function SumByCellColor(Cellrange As range, Cellcolor As Single) As
Single
Application.Volatile
n = 0
For Each RangeCell In Cellrange
If RangeCell.Interior.ColorIndex = Cellcolor Then
If IsNumeric(RangeCell.Value) Then n = n + RangeCell.Value
End If
Next
SumByCellColor = n
End Function

Also, see

http://www.xldynamic.com/source/xld.ColourCounter.html

--

HTH

RP

"SIGE" wrote in message
om...
Anyone an idea why my function does not work... it seems to unload...

Public Function SumByCellColor(Cellrange As range, Cellcolor As Single) As
Single
Application.Volatile
n = 0
For Each RangeCell In Cellrange
If RangeCell.Interior.ColorIndex = Cellcolor Then
If IsNumeric(RangeCell.Value) Then n = n + RangeCell.Value
End If
Next SumByCellColor = n
End Function

Thx in advance

SumByCellColor
 

"Harald Staff" wrote in message ...
"SIGE" skrev i melding
om...
Anyone an idea why my function does not work... it seems to unload...

Public Function SumByCellColor(Cellrange As range, Cellcolor As Single) As
Single
Application.Volatile
n = 0
For Each RangeCell In Cellrange

n and RangeCell are not declared

Next SumByCellColor = n

This can't do any good.
Also, coloring doesn't trig a calculation, you need an F9 or similar to make
a recalculation happen.

HTH. Best wishes Harald

Hi Guys,

Thx for the advises. I surely had my Variables declared.
It works fine... except with "conditional formatting"...
Why is this?

SumByCellColor
 

Colors produced with conditional formatting do not change the Colorindex
property - in fact, there is no property that reflects if conditional
formatting is currently being applied or which condition. You have to check
the condition that conditional formatting is using. Chip Pearson has
written a complex function that does this, but If you know the condition I
would just use that. If curious about Chip's approach

http://www.cpearson.com/excel.htm

go to the page index and look at the entries for conditional formatting.

--
Regards,
Tom Ogilvy

"SIGE" wrote in message
om...
"Harald Staff" wrote in message
...
"SIGE" skrev i melding
om...
Anyone an idea why my function does not work... it seems to unload...

Public Function SumByCellColor(Cellrange As range, Cellcolor As
Single) As
Single
Application.Volatile
n = 0
For Each RangeCell In Cellrange

n and RangeCell are not declared

Next SumByCellColor = n

This can't do any good.
Also, coloring doesn't trig a calculation, you need an F9 or similar to
make
a recalculation happen.

HTH. Best wishes Harald

Hi Guys,

Thx for the advises. I surely had my Variables declared.
It works fine... except with "conditional formatting"...
Why is this?