UDF not working

[NMitch]

Hi all,

This is for Excel 2013, and here is my code, which has worked before:

Public Function SumByColor(CellColor As Range, rRange As Range)    
    '   CellColor is the cell containing they colour type you are searching for
    '   rRange is the range of cells to add, if the colour matches
    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(c1, cSum)
        End If
     Next cl
     SumByColor = cSum
End Function

When using this in a macro-enabled workbook, the #NAME ? result appears.

The function used to be just a normal function, which resides in my PERSONAL.XLSB, but changed to PUBLIC to see if that made a difference.

I have a habit of doing things, like formulae, in lower case, then seeing if it changes to the proper case after pressing Enter. The UDF is SumByColor(), but the formula shows sumbycolor().

Can someone let me know what I'm doing wrong?

Thank you,

 

[Marcelo Branco]

Another possible problem:
Are the cells color manually or by conditional formatting?
If the latter, the UDF will not work, because a UDF does not "see" colored cells by CF.
However a Sub can "see" them using the DisplayFormat property.

M.

 

[NMitch]

Well spotted, Marcelo! Thank you!

Now I have numbers, which seem to add up precisely!

 

[NMitch]

Thanks, Marcelo, but no, they are not coloured by conditional formatting.

What I do now have as a problem is the SUMBYCOLOR() is rounding to the nearest $.

Should cSum be declared differently?

Thank you

 

[NMitch]

Ok, I think I've found the answer. Instead of declaring cSum as Integer, I've used Double, and it seems to be giving me the right answer.

I will need this variable to hold numbers to two decimal points, up to around 300m.