vba function that return #value

[ahmedGhnam]

I have this function that sum cells by colors from conditionally format but it return #value
i need help in this please.

Function SumByColor(sumCells As Range, sumColor As Range) As Integer
Dim TotalSum As Long
Dim sumColorValue As Long
sumColorValue = sumColor.DisplayFormat.Interior.ColorIndex
TotalSum = 0
For Each rCell In sumCells
    If rCell.DisplayFormat.Interior.ColorIndex = sumColorValue Then
            TotalSum = TotalSum + rCell.Value
    End If
Next rCell
SumByColor = TotalSum
End Function

 

[Fluff]

Hi & welcome to MrExcel.
You cannot use Display format inside a udf, you need to use a workaround like

Function SumByColor(sumCells As Range, sumColor As Range) As Long
Dim TotalSum As Long
Dim sumColorValue As Long
sumColorValue = Evaluate("getcfcolour(" & sumColor.Address(, , , 1) & ")")
TotalSum = 0
For Each rCell In sumCells
    If Evaluate("getcfcolour(" & rCell.Address(, , , 1) & ")") = sumColorValue Then
            TotalSum = TotalSum + rCell.Value
    End If
Next rCell
SumByColor = TotalSum
End Function
Function GetCFColour(cl As Range) As Long
   GetCFColour = cl.DisplayFormat.Interior.ColorIndex
End Function

 

[ahmedGhnam]

thank u

Fluff​

for your time

 

[Fluff]

You're welcome & thanks for the feedback.