Count the cells value by different color specified

问题:

Please need your help on count the cells value by different color specified.

In one sheet, some cells filled with red color, some cells filled with blue color, some cells filled with green color. The output should be cells count red color, cells count of blue color and cells count of green color separately.

This is what I've tried:

Function CountByColor(InputRange As Range, ColorRange As Range) As Long

    Dim cl As Range
    TempCount As Long
    ColorIndex As Integer

    ColorIndex = ColorRange.Cells(1, 1).Interior.ColorIndex TempCount = 0
    For Each cl In InputRange.Cells
        If cl.Interior.ColorIndex = ColorIndex
        Then
        TempCount = TempCount + 1
        End If
    Next cl
    Set cl = Nothing CountByColor = TempCount
End Function

回答1:

Your function works as expected:-

Function CountByColor(InputRange As Range, ColorRange As Range) As Long
    Dim cl As Range, TempCount As Long, ColorIndex As Integer
    ColorIndex = ColorRange.Cells(1, 1).Interior.ColorIndex
    TempCount = 0
    For Each cl In InputRange.Cells
        If cl.Interior.ColorIndex = ColorIndex Then
            TempCount = TempCount + 1
        End If
    Next cl
    Set cl = Nothing
    CountByColor = TempCount
End Function

Formula:

=CountByColor(A:A,A2)

Here A2 Cell is filled with Green and Green color index is 14

Result:

For my sheet, I got the result as

3

Basically you need to execute this formula for three times to get three results

=CountByColor(A:A,A2)   // A2 filled with Green
=CountByColor(A:A,A6)   // A6 filled with Red
=CountByColor(A:A,A9)   // A9 filled with Blue