SumByCellColor

[SIGE]

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

 

[Harald Staff]

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

 

[Bob Phillips]

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

 

[SIGE]

n and RangeCell are not declared


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?

 

[Tom Ogilvy]

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.