Counting suggest colour cells

[Buzzard2406]

Hi,

I need to count a large number of different colour cells in a spreadsheet. There are no numbers in the cels the cels are just different colours. Is there a method or formula I can use for this? Thank you 

[#2]

@Buzzard2406 

You'll need a custom VBA function for this.

Press Alt+F11 to activate the Visual Basic Editor.

Select Insert > Module to create a new module.

Copy this code into the module:

Function CountColor(Range2Count As Range, ColorCell As Range) As Long
    Dim Cell2Count As Range
    Dim Color2Count As Long
    Application.Volatile
    Color2Count = ColorCell.Interior.Color
    For Each Cell2Count In Range2Count
        If Cell2Count.Interior.Color = Color2Count Then
            CountColor = CountColor + 1
        End If
    Next Cell2Count
End Function

Switch back to Excel.

Use like this:

=CountColor($A$1:$A$15,C1)

to count the number of cells in A1:A15 that have the same fill color as cell C1.

Save the workbook as a macro-enabled workbook (*.xlsm).

Make sure that you allow macros when you open it.

Warning: the result of the formula will not be updated automatically when you change the fill color of one or more of the cells involved. This will happen the next time Excel recalculates. You can force this by pressing F9.