Count by Color?

[golf4]

Hi, everyone -

I have been expounding the virtues of this website to several people at work, and I swore to one of them that I have seen a posting regarding the ability to count cells by color.

Right now, she has a payroll spreadsheet, using different colors, where she has the cells coded with numbers. The formula she uses counts the cells with red (all coded with "1"s), blue (all coded as "2"s), etc. I remember a posting that, I believe, uses COUNTIF but I can't seem to find it through search. Can anyone help me on this one?

Thanks

 

[Tom Urtis]

Well that's good news. Keep in mind, if you define the range as ("E:E") then you are looping through 65,536 cells (and for 3 evaluations each, due to the 3 colors in your code). Consider making a reasonable judgment regarding the likelihood of not formatting past a certain row, and limit your range to that, so your run time is shortened.

 

[Jaafar Tribak]

Hi all

I have been trying for a while to come up with a UDF that counts cells by their color and that Updates automatically as soon as the color changes !
I searched the Board for a solution but couldn't find one.

I have achieved this by using a combination of UDF code, Worksheet event code and a Class module to trap the ccrpTimer events.

This object belongs to the ccrpTimer.dll Library.

If you don't have it on your System you can Download it from the following link : http://hem.passagen.se/fylke/?noframe

You will need to establish a reference to this Library via Tools>Reference.

This may sound like too much work for a small result ,however I personally found it a bit challenging.

Ok let's start:

Step1

Defining the UDF. Place this code in a Standard Module

' / / Function Purpose :
' ===============
' / / Count Cells By Interior Color.
' / / Uses ccrpTimer Object Via
' / / A Class Module ' TimerClass'

Public Tmr As TimerClass
Function CountColorCells(Rng As Range, RngColor As Range) As Integer

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

Step2

Insert a Class Module in your Project and name it TimerClass Via the Properties Window then assign it this Code:

' / / Code Defines Timer Event
' ====================

Public WithEvents T As ccrpTimer
Private Sub t_Timer(ByVal Milliseconds As Long)
    On Error Resume Next
    Application.CalculateFull
End Sub

Step3

Place this code in the Worksheet Module ( The worksheet that contain the Formula/Cells )

    ' / / Event Purpose :
    '  ============
    ' / /  Event To Set The Timer If Target Whithin
    ' / /  Range Of Which Colored Cells Are Counted.
    ' / /  Timer Disabled If Target Outside Range.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    
        Dim TrgtDpndnts, Rng As Range
        Dim T As ccrpTimer
        
        Application.EnableEvents = False
        On Error Resume Next
        Set TrgtDpndnts = Target.Dependents
        If Err.Number = 0 Then
            For Each Rng In Target.Dependents
                If Left(Rng.Formula, 16) = "=CountColorCells" Then
                        Set Tmr = New TimerClass
                        With Tmr
                            Set .T = New ccrpTimer
                            .T.Enabled = True
                            .T.Interval = 80 ' Adjust Interval to suit your System
                        End With
                        Exit For
                End If
            Next
        Else
            Tmr.T.Enabled = False
        End If
         Application.EnableEvents = True
     
    End Sub

That's all.

Drawbaks of this technic :

If when you select the Colored cells in order to change their color,the screen starts flickering a little bit .Try increasing the Timer Interval.

Also,these codes could slow down the WorkBook if is large and contains many Formulas.

See you!

 

[Doug Crumley]

I'm searching for something that counts the cell with a background color. I tried this formula but did not work for me. I want to count the number of cells in a column whose background is yellow. I just may be ignorant on how to make it work, I'm williing to learn, please help.

 

[golf4]

Hi, Doug -

I just saw your query, and someone may have already suggested this, but:

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

HTH,

Golf

 

[Hozz]

I have entered the code exactly as stated in Tom Urtis' first post, but whenever I try to enter the formula, I get the error '#NAME?'.

I have also tried the codes on www.cpearson.com website, but again, when i enter the formula's, I again get the same error.

What am I doing wrong?

 

[Frogggg]

I have been also able to make this work, but counting red squares only. If red is 3, what is lime? I can't work that bit out.

 

[g579]

Tom,

I've been using your function below and I would like to manipulate it to make more use out of it and I was wondering if you know how to do this. What I am trying to do is basically count the number of cells that are green in one column only if the corresponding cell in another column contains a certain text like "Office".

Basically, I only want to count the green cells in say column A if the corresponding cell in column B contains "Office". I also would like it to refresh automatically every time a change happens.

Thank you in advance for your help!!

Public Function CountIfColor(rng As Range, clrindx As Integer)
Dim Cell
CountIfColor = 0
For Each Cell In rng.Cells
If Cell.Interior.ColorIndex = clrindx Then
CountIfColor = CountIfColor + 1
End If
Next Cell
End Function

To return the quantity of red-shaded (non-conditionally formatted) cells in the range A1:A100 for example, this is the formula you'd type into a cell:

=CountIfColor(A1:A100,3)