How to count no. of Cells in MS Excell depending on their Fill Colors

I want to count the no. of cells in a row or column based of their background colors, e开发者_开发百科.g. how many are Red background, how many are Blue etc in a range of cells

---

It looks like there are ways to do this, using Excel VBA, but not natively using the COUNTIF function. The COUNTIF function uses the data in the cell as criteria - is there a data criteria in use for determining background color that could also be used for a COUNTIF function?

---

You have to use VBA (open VBA editor with Alt+F11)

1. First get the index of colors by runing this Sub:

   Sub showColorIndices()
   For i = 1 To 56
       Range("A" & i).Interior.ColorIndex = i
       Range("B" & i).Value = " " & i
   Next
   End Sub
   

You'll get something like this:

2. Then you can count the number of cells of a given color index with this Function:

   Function fnNbCellsColor(Plage As Range, ColorIndex As Integer) As Long
   
   Dim rCell As Range
   
   For Each rCell In Plage
       If rCell.Interior.ColorIndex = ColorIndex Then
           fnNbCellsColor = fnNbCellsColor + 1
       End If
   Next
   
   End Function
   

To count the number of blue cells, just write this formula in your sheet:

= fnNbCellsColor(D1:D20; 5)