Forum Discussion

Buzzard2406's avatar
Buzzard2406
Copper Contributor
Sep 19, 2023

Counting suggest colour cells

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 

Formulas and Functions
  • HansVogelaar's avatar
    HansVogelaar
    MVP
    Sep 19, 2023

    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.

Resources