Summing XColors Using UDF Function

[howard]

I have used the following code to sum color using UDF in Excel, but the result I get is zero.

See my code below as well as sample data. It would be appreciated if someone could assist me

Function SumColor(rColor As Range, rSumRange As Range)

Dim rCell As Range

Dim iCol As Integer

Dim vResult



iCol = rColor.Interior.ColorIndex



For Each rCell In rSumRange

If rCell.Interior.ColorIndex = iCol Then

vResult = vResult + 1

End If

Next rCell



CountColor = vResult

End Function

Summing Colors.xltm
	A	B	C	D
1	125.25
2	185.95
3	106.75
4	103.75
5	99.85
6
7	0
8
Sheet1

 

[Jonmo1]

Your code is fine...but you've got some naming problems..

Function SumColor(rColor As Range, rSumRange As Range)

CountColor = vResult

End Function

Those both need to be the same...

 

[howard]

Hi Jonmo1

Thanks for pointing out the error in my code. I have corrected this and attached sample data, but when summing the colored items, the result is zero. Please check & advise

Function SumColor(rColor As Range, rSumRange As Range)

Dim rCell As Range

Dim iCol As Integer

Dim vResult



iCol = rColor.Interior.ColorIndex



For Each rCell In rSumRange

If rCell.Interior.ColorIndex = iCol Then

vResult = vResult + 1

End If

Next rCell



SumColor = vResult

End Function




<b>Sheet1</b><br /><br /><table border="1" cellspacing="0" cellpadding="0" style="font-family:Calibri,Arial; font-size:11pt; background-color:#ffffff; padding-left:2pt; padding-right:2pt; "> <colgroup><col style="font-weight:bold; width:30px; " /><col style="width:64px;" /></colgroup><tr style="background-color:#cacaca; text-align:center; font-weight:bold; font-size:8pt; "><td >*</td><td >A</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >1</td><td style="text-align:right; ">125.25</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >2</td><td style="background-color:#0066cc; text-align:right; ">185.95</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >3</td><td style="text-align:right; ">106.75</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >4</td><td style="background-color:#0066cc; text-align:right; ">103.75</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >5</td><td style="text-align:right; ">99.85</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >6</td><td >*</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >7</td><td style="text-align:right; ">0</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >8</td><td >*</td></tr></table><br /><table style="font-family:Arial; font-size:10pt; border-style: groove ;border-color:#00ff00;background-color:#fffcf9; color:#000000; "><tr><td ><b>Spreadsheet Formulas</b></td></tr><tr><td ><table border = "1" cellspacing="0" cellpadding="2" style="font-family:Arial; font-size:9pt;"><tr style="background-color:#cacaca; font-size:10pt;"><td >Cell</td><td >Formula</td></tr><tr><td >A7</td><td >=sumcolor(A2,A1:A6)</td></tr></table></td></tr></table> <br />Excel tables to the web - Excel Jeanie Html 4

 

[meldoc]

Hello

That works for me, but it only counts the coloured cells, it returns 2.

To sum the range change this line of code, as below.

vResult = vResult + rCell.Value

 

[Jonmo1]

Are your cells colored via conditional formatting?
If so, this function will not work.

If this is the case, you'd be better off designing your UDF to count the condition that causes the color, not the color itself.

 

[ZVI]

Hi Brian,
Your function would be:

Function SumColor(rColor As Range, rSumRange As Range) As Double
  
  ' This calls the function each time the cells change or sheet calculates
  Application.Volatile
  
  ' Main
  Dim rCell As Range, iCol As Integer
  iCol = rColor.Interior.ColorIndex
  For Each rCell In rSumRange
    If rCell.Interior.ColorIndex = iCol Then
      SumColor = SumColor + rCell.Value
    End If
  Next
  
End Function

Please take into account that changing of the cells colors doesn't raise recalculation of this UDF.
Changing of any cells values or F9 pressing is required for updating of UDF result.

 

[meldoc]

A very professional example.

Thank you Vladimir.

 

[howard]

Hi Guys

Thanks for the help. The UDF function now works perfectly