Counting Colors

[nathanpeattie]

I was looking to have A1,B1,C1,D1 shaded green and E1 shaded red.

I would like a code that will give a value in H1 for how many green
& I1 for how many red.

Can this be done.

I don't want to use text the the fields just colors except for the value.

Thanks

 

[Oaktree]

See:

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

 

[nathanpeattie]

Trying

Function CountByColor(InRange As Range, _
WhatColorIndex As Integer, _
Optional OfText As Boolean = False) As Long
'
' This function return the number of cells in InRange with
' a background color, or if OfText is True a font color,
' equal to WhatColorIndex.
'
Dim Rng As Range
Application.Volatile True

For Each Rng In InRange.Cells
If OfText = True Then
CountByColor = CountByColor - _
(Rng.Font.ColorIndex = WhatColorIndex)
Else
CountByColor = CountByColor - _
(Rng.Interior.ColorIndex = WhatColorIndex)
End If
Next Rng

End Function


but will only count 1 color I want to return 2 values

 

[Oaktree]

Not sure I understand...per your original post, wouldn't you use two formulas (seting the formula in H1 to count Reds and the formula in I1 to count Greens)?

 

[nathanpeattie]

yes that what I want h1 for green and I1 for red

 

[nathanpeattie]

heres what I got

=COUNTBYCOLOR(A4:A117,3,FALSE) this in I1 for "RED"
=COUNTBYCOLOR(A4:A117,50,FALSE) this in H1 for "GREEN"

so I think I got it however if I change a green to a red the value does not change untill I click in the value field how can I fix this?

 

[Oaktree]

There really isn't a way to. Changing a background color (or any format for that matter) doesn't cause Excel to update its calculations.

You can experiment with putting something like this in the sheet module:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
ActiveSheet.Calculate
End Sub

which will cause your sheet to recalculate whenever you select a different cell...but, this may or may not be practical for what you're doing.

 

[nathanpeattie]

now how can I make

=COUNTBYCOLOR(A4:A117,50,FALSE) count both colors and return a value

EG:

=COUNTBYCOLOR(A4:A117,3,50,FALSE) this does not work!

 

[nathanpeattie]

I figured It Out!

Thanks for the help!
This is a great board!