help with formula

Visual Basic.

Step 1: Create the function

Press Alt + F11

Insert → Module

------------------------------------- Paste this code: -------------------------------------

Function CountByColor(rng As Range, colorCell As Range) As Long

    Dim c As Range

    Dim count As Long

    For Each c In rng

        If c.Interior.Color = colorCell.Interior.Color Then

            count = count + 1

        End If

    Next c

    CountByColor = count

End Function

-------------------------------------Step 2: -------------------------------------

Use your function in Excel by inserting it in a field (example is currently only for 1 column from A1 to A100, B1 → cell with the color you want to count )

=CountByColor(A1:A100, B1)