I am trying to write a function that will calculate the sum of cells i
a range depending on their background color.
I have looked on the net and found some sample code, tried it and i
doesnt work.

the code is as follows:

Function colorfunction(rcolor As Range, rRange As Range, Optional SU
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
#VALUE!

Any help on this would be great.

Cheer

--
Message posted from http://www.ExcelForum.com

Have a look here
http://www.cpearson.com/excel/colors.htm

--
Paul B
Always backup your data before trying something new
Please post any response to the newsgroups so others can benefit from it
Feedback on answers is always appreciated!
Using Excel 2000 & 97
** remove news from my email address to reply by email **
"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/

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/

Thank you both for your help, the code works fine now.

Have a good da

--
Message posted from http://www.ExcelForum.com