Home |
Search |
Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Sum depending on cell background color
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Sum depending on cell background color
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/ |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Sum depending on cell background color
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/ |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Sum depending on cell background color
Thank you both for your help, the code works fine now.
Have a good da -- Message posted from http://www.ExcelForum.com |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Changing Cell Background Color Depending on the Number | Excel Discussion (Misc queries) | |||
how do I sum depending on the background color of cells in Excel? | Excel Discussion (Misc queries) | |||
how do i change cell background colour depending on its content? | Excel Discussion (Misc queries) | |||
Different background color depending on x-axis value | Charts and Charting in Excel | |||
Default Border, Font Color, and Cell Background Color | Excel Discussion (Misc queries) |