Prev: Sorting Columns by number of characters
Next: Resize object by HxW using pixels
From: dwake on 5 May 2010 17:24
Is it possible to count the number of cells in a column by color? For
example if there are ten cells in a specific column that are colored red, can
I insert a formula to get that sum?


From: L. Howard Kittle on 5 May 2010 17:39

Chip can tell you about everything you will need to know on this subject.

http://www.cpearson.com/excel/colors.aspx


Or you can probably edit this down to only count the red cells. You will
need to name ther range to be counted Data.

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

"dwake" <dwake(a)discussions.microsoft.com> wrote in message
news:CFB3F38B-E817-4BE4-B178-E0DC444D7107(a)microsoft.com...
> Is it possible to count the number of cells in a column by color? For
> example if there are ten cells in a specific column that are colored red,
> can
> I insert a formula to get that sum?
>
>


From: L. Howard Kittle on 5 May 2010 17:41
Forgot to say, my code does not work if the cells are colored by conditional
formatting.

Regards,
Howard

"dwake" <dwake(a)discussions.microsoft.com> wrote in message
news:CFB3F38B-E817-4BE4-B178-E0DC444D7107(a)microsoft.com...
> Is it possible to count the number of cells in a column by color? For
> example if there are ten cells in a specific column that are colored red,
> can
> I insert a formula to get that sum?
>
>


 | 
Pages: 1
Prev: Sorting Columns by number of characters
Next: Resize object by HxW using pixels