Can't get my UDF to return a decimal - Sum Value by color index

[MasterK]

I need to sum the values of a range by the background color of the cell. So I created 2 UDFs. The first one that creates the color index works fine:

Function ColorIndex(CellColor As Range)
ColorIndex = CellColor.Interior.ColorIndex
End Function

The Second UDF works fine as long as we're summing whole numbers, but will not sum decimals. My suspicion is partly due to line 3 "As Integer", but I changed that to
Single, Double, Long. No change. A cell entry of 0.5 returns a "0" and 1.5 returns "2". I only need to be as precise as 1 decimal (tenths) place.

Function SumByColor(CellColor As Range, rRange As Range)
Dim cSum As Long
Dim ColIndex As Integer
ColIndex = CellColor.Interior.ColorIndex
For Each cl In rRange
  If cl.Interior.ColorIndex = ColIndex Then
    cSum = WorksheetFunction.Sum(cl, cSum)
  End If
Next cl
SumByColor = cSum
End Function

I appreciate any assistance.

 

[jmacleary]

Try this slight variation:

Function SumByColor(CellColor As Range, rRange As Range)
Dim cSum As Long
Dim ColIndex As Integer
ColIndex = CellColor.Interior.ColorIndex
cSum=0
For Each cl In rRange
  If cl.Interior.ColorIndex = ColIndex Then
    cSum = cSum+cl.Value
  End If
Next cl
SumByColor = cSum
End Function

 

[Joe4]

Try declaring both your function and your sum variables as Single or Double, i.e.

Function SumByColor(CellColor As Range, rRange As Range) as Double
Dim cSum As Double
Dim ColIndex As Integer
ColIndex = CellColor.Interior.ColorIndex
For Each cl In rRange
  If cl.Interior.ColorIndex = ColIndex Then
    cSum = WorksheetFunction.Sum(cl, cSum)
  End If
Next cl
SumByColor = cSum
End Function

 

[DanteAmor]

Some recommendations:

Do not name functions with the same name of an attribute or method, it may conflict.
Function ColorIndex(CellColor As Range)
ColorIndex = CellColor.Interior.ColorIndex
End Function

In the second function, it is not necessary to declare the cSum and ColIndex variables, you can perform the sum directly, in fact the variable cl is the only one that you had to declare, it can be as follows:

Function SumByColor(CellColor As Range, rRange As Range) As Double
  Dim cl As Range
  For Each cl In rRange
    If cl.Interior.ColorIndex = CellColor.Interior.ColorIndex Then
      SumByColor = SumByColor + cl.Value
    End If
  Next cl
End Function

 

[MasterK]

Try declaring both your function and your sum variables as Single or Double, i.e.

Function SumByColor(CellColor As Range, rRange As Range) as Double
Dim cSum As Double
Dim ColIndex As Integer
ColIndex = CellColor.Interior.ColorIndex
For Each cl In rRange
  If cl.Interior.ColorIndex = ColIndex Then
    cSum = WorksheetFunction.Sum(cl, cSum)
  End If
Next cl
SumByColor = cSum
End Function

This did it. I was only changing one of them.