countif a cell is a specific color shade

Soliciting help on counting a cell given a specific color shade. I was going to use (the range is an example): COUNT IF (A1:A10, cell("color",[ --- ])) ... but where do I find a defined color pal...

excel

HansVogelaar

May 12, 2022

KennyMikus

You need a custom VBA function for this. Here is a simple example:

Function CountIfColor(CountRange As Range, ColorCell As Range) As Double
    Dim ACell As Range
    Dim TheColor As Long
    Application.Volatile
    TheColor = ColorCell.Interior.Color
    For Each ACell In CountRange
        If ACell.Interior.Color = TheColor Then
            CountIfColor = CountIfColor + 1
        End If
    Next ACell
End Function

Let's say you want to count the number of cells in A1:D10 that have the same fill color as K1.

The formula would be

=CountIfColor(A1:D10,K1)