CODEWITHSUNDEEP
vbaexcel
user3002486
user3002486

Reputation: 431

Function: Error Sub or Function not defined

Function CountColor(rColor As Range, rSumRange As Range)
    Dim rCell As Range
    Dim iCol As Integer
    Dim vResult As Integer
    iCol = rColor.Interior.ColorIndex

    For Each rCell In rSumRange
        If rCell.Interior.ColorIndex = iCol Then
            vResult = 1
        Else 
            vResult =  0
        End If
    Next rCell 
    CountColor = vResult
End Function

I try typing "=CountColor(A1, A2)" but I always get the error "Sub or function not defined" Why is this? I've been stuck on this for hours.

Upvotes: 0

Views: 474

Answers (1)

Davesexcel
Davesexcel

Reputation: 6982

I could not reproduce the error that you are experiencing.

If you use the code as you have it, the result will not be accurate, for example: =CountColor(A1,B1:B20) will only give you a result of 1 or 0 because you are not adding the results together.

If you are just comparing the interior colors, you don't really need to use interior.colorindex, just interior.color should work, so I changed iCol as string

else is not required in your if statement.

I also added Application.volatile to the code, so it will calculate when the sheet calculates.

Function CountColor(rColor As Range, rSumRange As Range)
    Dim rCell As Range
    Dim iCol As String
    Dim vResult As Integer

    iCol = rColor.Interior.Color
    Application.Volatile

    For Each rCell In rSumRange
        If rCell.Interior.Color = iCol Then
            vResult = 1 + vResult
            '        Else
            '            vResult = 0
        End If
    Next rCell

    CountColor = vResult

End Function

Upvotes: 1

Related Questions