Count a list of cells with the same background color


Each cell contains some text and a background color. So I have some cells that are blue and some that are red. What function do I use to count the number of red cells?

I have tried =COUNTIF(D3:D9,CELL("color",D3)) with no success (Where D3 is red).


Yes VBA is the way to go.

But, if you don't need to have a cell with formula that auto-counts/updates the number of cells with a particular colour, an alternative is simply to use the 'Find and Replace' function and format the cell to have the appropriate colour fill.

Hitting 'Find All' will give you the total number of cells found at the bottom left of the dialogue box.

enter image description here

This becomes especially useful if your search range is massive. The VBA script will be very slow but the 'Find and Replace' function will still be very quick.

By : qoheleth

I was needed to solve absolutely the same task. I have divided visually the table using different background colors for different parts. Googling the Internet I've found this page Unfortunately it doesn't solve the issue because ColorIndex refers to some unpredictable value, so if some cells have nuances of one color (for example different values of brightness of the color), the suggested function counts them. The solution below is my fix:

Function CountBgColor(range As range, criteria As range) As Long
    Dim cell As range
    Dim color As Long
    color = criteria.Interior.color
    For Each cell In range
        If cell.Interior.color = color Then
            CountBgColor = CountBgColor + 1
        End If
    Next cell
End Function
By : jsxt

I just created this and it looks easier. You get these 2 functions:

=GetColorIndex(E5)  <- returns color number for the cell

from (cell)

=CountColorIndexInRange(C7:C24,14) <- returns count of cells C7:C24 with color 14

from (range of cells, color number you want to count)

example shows percent of cells with color 14

=ROUND(CountColorIndexInRange(C7:C24,14)/18, 4 )

Create these 2 VBA functions in a Module (hit Alt-F11)

open + folders. double-click on Module1

Just paste this text below in, then close the module window (it must save it then):

Function GetColorIndex(Cell As Range)
  GetColorIndex = Cell.Interior.ColorIndex
End Function

Function CountColorIndexInRange(Rng As Range, TestColor As Long)
  Dim cnt
  Dim cl As Range
  cnt = 0

  For Each cl In Rng
    If GetColorIndex(cl) = TestColor Then
      Rem Debug.Print ">" & TestColor & "<"
      cnt = cnt + 1
    End If

  CountColorIndexInRange = cnt

End Function

This video can help you solving your question :)
By: admin