Counting Cells with colors

  • Thread starter Thread starter JimW
  • Start date Start date
J

JimW

Is there a formula that will count cells that contain different colors. Blank
with no data just colored.
 
There is no built in function in Excel to do that, but there is a macro you
can use.

If you want it, you can do a search in the search for box with count color.
 
I don't think you can do that with a formula. Here is some code that may be
tweeked to suit.

Sub ColorCount()
'Counts the number of colored
'cells in a range named Data.
Dim Blue5 As Integer
Dim Red3 As Integer
Dim Green4 As Integer
Dim Yellow6 As Integer
Dim Cell As Range

For Each Cell In Range("Data") '("B1:F11")
If Cell.Interior.ColorIndex = 5 Then
Blue5 = Blue5 + 1
ElseIf Cell.Interior.ColorIndex = 3 Then
Red3 = Red3 + 1
ElseIf Cell.Interior.ColorIndex = 4 Then
Green4 = Green4 + 1
ElseIf Cell.Interior.ColorIndex = 6 Then
Yellow6 = Yellow6 + 1
End If
Next

Range("A1").Value = Blue5 & " Blue"
Range("A2").Value = Red3 & " Red"
Range("A3").Value = Green4 & " Green"
Range("A4").Value = Yellow6 & " Yellow"

MsgBox " You have: " & vbCr _
& vbCr & " Blue " & Blue5 _
& vbCr & " Red " & Red3 _
& vbCr & " Green " & Green4 _
& vbCr & " Yellow " & Yellow6, _
vbOKOnly, "CountColor"
End Sub

HTH
Regards,
Howard
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Excel VBA - counting cells by colors and names to it in two different rows. 0
Counting of cells with a specific color filled 9
Counting Cell Color? 2
=Countif(range, criteria) 2
Counting Colored Cells that are Conditional Formated 3
Change Color of cell 3
Excel VBA 1
Count number of values 2

Back
Top