VBA Code returns NAME? ERROR

Question

I have tried 3 different VBA codes for counting cells by color and keep getting NAME?

Answer 1

Function GetCellColor(xlRange As Range)

    Dim indRow, indColumn As Long

    Dim arResults()

    Application.Volatile

    If xlRange Is Nothing Then

        Set xlRange = Application.ThisCell

    End If

    If xlRange.Count > 1 Then

      ReDim arResults(1 To xlRange.Rows.Count, 1 To xlRange.Columns.Count)

       For indRow = 1 To xlRange.Rows.Count

         For indColumn = 1 To xlRange.Columns.Count

           arResults(indRow, indColumn) = xlRange(indRow, indColumn).Interior.Color

         Next

       Next

     GetCellColor = arResults

    Else

     GetCellColor = xlRange.Interior.Color

    End If

End Function

Function GetCellFontColor(xlRange As Range)

    Dim indRow, indColumn As Long

    Dim arResults()

    Application.Volatile

    If xlRange Is Nothing Then

        Set xlRange = Application.ThisCell

    End If

    If xlRange.Count > 1 Then

      ReDim arResults(1 To xlRange.Rows.Count, 1 To xlRange.Columns.Count)

       For indRow = 1 To xlRange.Rows.Count

         For indColumn = 1 To xlRange.Columns.Count

           arResults(indRow, indColumn) = xlRange(indRow, indColumn).Font.Color

         Next

       Next

     GetCellFontColor = arResults

    Else

     GetCellFontColor = xlRange.Font.Color

    End If

End Function

Function CountCellsByColor(rData As Range, cellRefColor As Range) As Long

    Dim indRefColor As Long

    Dim cellCurrent As Range

    Dim cntRes As Long

    Application.Volatile

    cntRes = 0

    indRefColor = cellRefColor.Cells(1, 1).Interior.Color

    For Each cellCurrent In rData

        If indRefColor = cellCurrent.Interior.Color Then

            cntRes = cntRes + 1

        End If

    Next cellCurrent

    CountCellsByColor = cntRes

End Function

Function SumCellsByColor(rData As Range, cellRefColor As Range)

    Dim indRefColor As Long

    Dim cellCurrent As Range

    Dim sumRes

    Application.Volatile

    sumRes = 0

    indRefColor = cellRefColor.Cells(1, 1).Interior.Color

    For Each cellCurrent In rData

        If indRefColor = cellCurrent.Interior.Color Then

            sumRes = WorksheetFunction.Sum(cellCurrent, sumRes)

        End If

    Next cellCurrent

    SumCellsByColor = sumRes

End Function

Function CountCellsByFontColor(rData As Range, cellRefColor As Range) As Long

    Dim indRefColor As Long

    Dim cellCurrent As Range

    Dim cntRes As Long

    Application.Volatile

    cntRes = 0

    indRefColor = cellRefColor.Cells(1, 1).Font.Color

    For Each cellCurrent In rData

        If indRefColor = cellCurrent.Font.Color Then

            cntRes = cntRes + 1

        End If

    Next cellCurrent

    CountCellsByFontColor = cntRes

End Function

Function SumCellsByFontColor(rData As Range, cellRefColor As Range)

    Dim indRefColor As Long

    Dim cellCurrent As Range

    Dim sumRes

    Application.Volatile

    sumRes = 0

    indRefColor = cellRefColor.Cells(1, 1).Font.Color

    For Each cellCurrent In rData

        If indRefColor = cellCurrent.Font.Color Then

            sumRes = WorksheetFunction.Sum(cellCurrent, sumRes)

        End If

    Next cellCurrent

    SumCellsByFontColor = sumRes

End Function

Answer 2

Have you copied VBA code in a standard module?

Answer 3

=CountCellsByColor(a1:a25,a30)    a1:a30= the range to be counted and a30= the cell with the color that I want counted.

Answer 4

Your all 4 functions are working correctly.

How are you callng your functions..Can you give me the formula which you are using?

Answer 5

Could you please post your VBA code so that we can study and let you know the cause of the error?