Counting cells filled with a certain color

[mister_speedys]

I want to write a formula to count cells that are "filled" with a particular color. I have a group of cells, some filled red some filled blue and some filled green and I want to count only the red filled cells. Can anyone help me wright a formula for this ???

 

[Paul B]

I cant take the credit for this, but I don’t remember where it came from
Once you have the code below in a module, you can use this formula:
=countcolor(A1:A99,D2)
Where A1:A99 is the range you want to count and D2 has the background format that you want to count.
Note this will not update automatically if you change color in D2 or put more formats in your range, you have to clickin the cell with the formula,click in the formula bar and hit enter to make it recalculate

Function CountColor(Rng As Range, RngColor As Range) As Integer
Dim Cll As Range
Dim Clr As Long
Clr = RngColor.Range("A1").Interior.Color
For Each Cll In Rng
If Cll.Interior.Color = Clr Then
CountColor = CountColor + 1
End If
Next Cll
End Function

 

[NateO]

In addition, if you want this function recalculate you need to add volatility via:<pre>
Application.Volatile</pre>

Add this after the variable declarations.

Paul, did you get the userform to work for your 2nd criteria?

_________________
Cheers,<font size=+2><font color="red"> Nate<font color="blue"> Oliver</font></font></font>
This message was edited by NateO on 2002-07-21 10:13

 

[Juan Pablo González]

Also, to force a recalculation, you can use

Control Alt F9

Another trick you could use, besides the Application.volatile thing in the code is to use something like

=COUNTCOLOR(A1:A10,B1)+NOW()*0

which will make the function recalculate when you change any cell.

 

[Dave Hawley]

Hi Mister_Speedy


I have 2 functions here, one that counts by color and one that Sums by color

http://www.ozgrid.com/VBA/Sum.htm

I even have one here that allows you to sort by color
http://www.ozgrid.com/VBA/Sort.htm

I personally wouldn't make the function volatile, as it still wont force a recalculation when a cells backround fill is changed. But it will make the UDF recalculate each time to type into any cell on any sheet,this can slooowww down Excel a lot on a UDF that loops through ranges. See my Objective look at Custom Functions here:

http://www.ozgrid.com/VBA/Functions.htm

_________________
Regards
Dave Hawley
Xl Add-ins, with free File Size Reducer
40+ more here
Xl Training
This message was edited by Dave Hawley on 2002-07-21 19:17