SumByColour displays #VALUE!

Hi,

I was wondering if anyone could help please. I have a spreadsheet and I am trying to sum by colour based on the colour of the cells that is defined by conditional formatting. I am not even close to being an expert so have cobbled some VBA code together by research on google.

I am so close when I look at the result in the insert function it gives me the number I am after but when displayed in the spreadsheet I am getting #VALUE! see images, all values are formatted as a number with no spaces etc.

The VBA code used is as follows. Please help it is driving me crazy!!!! Thanks in advance

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

Capture.JPG
Capture2.JPG