Function to sum cells by color

Question

Below I placed a function code to sum cells by color. I wrote a sub to execute the function.

I get a Run-time '1004' error. I don't know what part is causing the error. I can't see the issue.

    Function ColorFunction(rColor As Range, rRange As Range, Optional SUM As Boolean)
    Dim rCell As Range
    Dim lCol As Long
    Dim vResult

    lCol = rColor.Interior.ColorIndex

    If SUM = True Then
        For Each rCell In rRange
            If rCell.Interior.ColorIndex = lCol Then
                vResult = WorksheetFunction.SUM(rCell, vResult)
            End If
        Next rCell
    Else
        For Each rCell In rRange
            If rCell.Interior.ColorIndex = lCol Then
                vResult = 1 + vResult
            End If
        Next rCell
    End If

    ColorFunction = vResult
    End Function

    Sub sumbycolor()

    NextRow = Range("B" & Rows.Count).End(xlUp).Row + 9
    Range("B" & NextRow).Select
    With Selection.Interior
        .Pattern = xlSolid
        .PatternColorIndex = xlAutomatic
        .Color = 65535
        .TintAndShade = 0
        .PatternTintAndShade = 0
    End With

    Dim lr As Long, critRange As String, sumRange As String
    lr = Cells(Rows.Count, "O").End(xlUp).Row
    sumRange = Range("O2:O" & lr).Address

    CellColor = Range("B" & Rows.Count).End(xlUp).Row + 9

    NextRow = Range("C" & Rows.Count).End(xlUp).Row + 9
    Range("C" & NextRow).Select
    ActiveCell.FormulaR1C1 = "=ColorFunction(" & CellColor & "," & sumRange & ",TRUE)"

    End Sub

Answer 1

I've put together a code snippet that incorporates changes I mentioned in my comments to your question. I created a string to hold the formula for ease in debugging.

...
Dim lr As Long, critRange As String, sumRange As String
Dim intCellColorRow As Integer
Dim strCellColorRange As String
Dim strFormula As String

lr = Cells(Rows.Count, "O").End(xlUp).Row
sumRange = Range("O2:O" & lr).Address

intCellColorRow = Range("B" & Rows.Count).End(xlUp).Row + 9
strCellColorRange = Range("B" & intCellColorRow).Address

NextRow = Range("C" & Rows.Count).End(xlUp).Row + 9
Range("C" & NextRow).Select
strFormula = "=ColorFunction(" & strCellColorRange & "," & sumRange & ",TRUE)"
ActiveCell.Formula = strFormula
...