Excel Office 365 - SumByColor and Rounding

[EMGVT]

I know there are prior posts to this, but I am having issues with Excel rounding up when trying to do sum by color. This is the code that was originally suggested that I use:

Function SumColor(SumRange As Range, ColorCode As Range)
Dim ColorCodeValue As Integer
Dim TotalSum As Long
ColorCodeValue = ColorCode.Interior.ColorIndex
Set rCell = SumRange
For Each rCell In SumRange
If rCell.Interior.ColorIndex = ColorCodeValue Then
TotalSum = TotalSum + rCell.Value
End If
Next rCell
SumColor = TotalSum
End Function


I have very basic knowledge of VBA, so any help would be highly appreciated!

 

[Osvaldo Palmeiro]

Try changing TotalSum type from Long to Double.

 

[Domenic]

The reason that the value returned by SumColor is sometimes rounded up is that you've declared TotalSum as Long. As such, the variable can't store decimal numbers. Therefore, declare it as Double. I would also suggest that you declared the return type of the SumColor function as Double as well. Also, I would suggest that you use the Color property of the Interior object, instead of the ColorIndex property so that you can refer to the complete palette of colors. This would mean that you would have to declare ColorCodeValue as Long. Try the following instead...

Function SumColor(SumRange As Range, ColorCode As Range) As Double

    Dim ColorCodeValue As Long
    Dim TotalSum As Double
    Dim rCell As Range
   
    ColorCodeValue = ColorCode.Interior.Color
   
    For Each rCell In SumRange
        If rCell.Interior.Color = ColorCodeValue Then
            TotalSum = TotalSum + rCell.Value
        End If
    Next rCell
   
    SumColor = TotalSum
   
End Function

Hope this helps!