You left out .colorindex
If rcell.Interior.ColorIndex = lCol Then '<-- fixed
Your code without the above resulted in a #Value! error.
I don't think it is a good ideal to have your own variable named SUM,
but that does not affect anything in your macro.
Assume that in what you have the first row is not RED, but it should
be redone to include the break character which actually includes a space " _"
so it can be posted without problems.
Function colorfunction(rcolor As Range, rRange As Range, _
Optional bSUM As Boolean)
Examples:
=colorfunction(A1,B1:B8,TRUE)
=colorfunction(A1,B1:B8,FALSE)
=colorfunction(A1,B1:B8) --- uses False as default
---
HTH,
David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001]
My Excel Pages:
http://www.mvps.org/dmcritchie/excel/excel.htm
Search Page:
http://www.mvps.org/dmcritchie/excel/search.htm
"gwag17 " wrote in message ...
I am trying to write a function that will calculate the sum of cells in
a range depending on their background color.
I have looked on the net and found some sample code, tried it and it
doesnt work.
the code is as follows:
Function colorfunction(rcolor As Range, rRange As Range, Optional SUM
As Boolean)
Dim rcell As Range
Dim lCol As Long
Dim vresult
lCol = rcolor.Interior.ColorIndex
If SUM = True Then
For Each rcell In rRange
If rcell.Interior = lCol Then
vresult = WorksheetFunction.SUM(rcell, vresult)
End If
Next rcell
Else
For Each rcell In rRange
If rcell.Interior.ColorIndex = lCol Then
vresult = 1 + vresult
End If
Next rcell
End If
colorfunction = vresult
End Function
The count part of this works, however if i try the SUM, it gives me a
#VALUE!
Any help on this would be great.
Cheers
---
Message posted from http://www.ExcelForum.com/