Counting ROWS by color

[Von Pookie]

First thing this morning, I was asked if there was a way to count by colors, which I know there are having seen it on this board many times

However, he wants to know how many ROWS have text of a certain color--not the number of cells. I can't figure out how to update any of the codes that I've found to do so.

He needs to have it count how many rows of each color, not just one specific one. So how many rows have blue text, how many have green text, etc.

I found this on Chip Pearson's site, but like I said, I don't know how to change it for what he needs:

The following function will return the number of cells in a range that have either an Interior (background) or Font of a specified color. InRange is the range of cells to examine, WhatColorIndex is the ColorIndex value to count, and OfText indicates whether to return the ColorIndex of the Font (if True) or the Interior (if False).

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

You can call this function from a worksheet cell with a formula like
=COUNTBYCOLOR(A1:A10,3,FALSE)

Any ideas?

 

[Juan Pablo González]

Kristy, one question...

When you say 'Entire Row', the row must have the same font color or each cell in the row can have a different color ?

 

[Andrew Poulsom]

Not so short and sweet, but seems to work:

Function CountRowsByColor(InRange As Range, WhatColorIndex As Integer, Optional OfText As Boolean = False) As Long
' This function return the number of rows in InRange with
' a background color, or if OfText is True a font color,
' equal to WhatColorIndex.
    Dim Rng As Range
    Dim r As Long
    Dim Found As Boolean
    Application.Volatile True
    r = InRange.Cells(1, 1).Row
    Found = False
    For Each Rng In InRange.Cells
        If Rng.Row = r Then
            If Found = False Then
                If OfText = True Then
                    If Rng.Font.ColorIndex = WhatColorIndex Then
                        CountRowsByColor = CountRowsByColor + 1
                        Found = True
                    End If
                Else
                    If Rng.Interior.ColorIndex = WhatColorIndex Then
                        CountRowsByColor = CountRowsByColor + 1
                        Found = True
                    End If
                End If
            End If
        Else
            r = Rng.Row
            Found = False
        End If
    Next Rng
End Function

 

[Von Pookie]

Kristy, one question...

When you say 'Entire Row', the row must have the same font color or each cell in the row can have a different color ?

From what I gathered, it's each cell in the row has the same font color.

I haven't gotten the file from him yet to try out your suggestion, Andrew. I'll let you know how it goes

 

[Von Pookie]

Ok...heeeeeeeelp me, I'm stupid.


A little backstory: I've got the file now, and it looks like the only colors used are green, blue, pink, red, and black. And the current range is A2:BD759. I'm assuming this file will continue to have rows added to it.

Anyway, I'm trying to test Andrew's code, but I can't remember how to call a function. I tried using =COUNTBYCOLOR(A2:BD759,3,FALSE) that is on Chip's page, and even tried making a module to call the function, but I can't get anything to work.

The module I tried was just:

Sub CountColors()

Call CountRowsByColor

End Sub

but it says 'sub or function not defined' and I've got no clue how to fix it.

 

[Andrew Poulsom]

The formula:

=COUNTBYCOLOR(A2:BD759,3,FALSE)

should work if you have the Function in a General module.

 

[Von Pookie]

Ah...that would help I guess

I had put the function in ThisWorkbook, AND forgot to add "rows" to the formula.

But it doesn't seem to be working...it just gives me an answer of 0.

Plus, how could I change this so that it gives me a breakdown for each color?

I just now was asked to make a chart for this as well. That, I should be able to do, but I need the count for each color so I can average them out.

 

[Juan Pablo González]

Kristy, can I suggest a different approach ?

Use this function:<font face=Courier New><SPAN style="color:#00007F">Function</SPAN> GETCOLOR(Rng<SPAN style="color:#00007F">As</SPAN> Range,<SPAN style="color:#00007F">Optional</SPAN> Text<SPAN style="color:#00007F">As</SPAN><SPAN style="color:#00007F">Boolean</SPAN> =<SPAN style="color:#00007F">False</SPAN>)<SPAN style="color:#00007F">As</SPAN><SPAN style="color:#00007F">Variant</SPAN>
    <SPAN style="color:#00007F">If</SPAN> Text<SPAN style="color:#00007F">Then</SPAN>
        GETCOLOR = Rng.Font.ColorIndex
    <SPAN style="color:#00007F">Else</SPAN>
        GETCOLOR = Rng.Interior.ColorIndex
    <SPAN style="color:#00007F">End</SPAN><SPAN style="color:#00007F">If</SPAN><SPAN style="color:#00007F">End</SPAN><SPAN style="color:#00007F">Function</SPAN></FONT>

like this:

newLeaseExamples.xls
	A	B	C	D	E	F	G	H	I
1								Color
2	1	1	1	1	1	1		-4105	Automatic
3	1	1	1	1	1	1		3	Red
4	1	1	1	1	1	1		-4105
5	1	1	1	1	1	1		0	Null(Differentcolors)
6	1	1	1	1	1	1		-4105
7	1	1	1	1	1	1		-4105
8	1	1	1	1	1	1		6	Yellow
9	1	1	1	1	1	1		-4105
10	1	1	1	1	1	1		5	Blue
Hoja1

then, you can use the column I to generate a Pivot Table. The result in row 5 is because of the question I made first... if the range that the function checks has different colors, the function returns Null.

I think this approach is better than counting each different cell in the spreadsheet.

 

[Andrew Poulsom]

I think this approach is better than counting each different cell in the spreadsheet.

I agree.

 

[Von Pookie]

Ok, that one's working.

I've never made a pivot table before, though, but I'm not really sure if I need one.

I've got what number = what color now using the function, couldn't I just do a COUNTIF to get how many of each color at this point?

Edit: Like so?

test.xls
	A	B	C	D
3		ColorIndex
4	GREEN=JUDDER/GREASE	10	499
5	BLUE=REOPEN/HOUSING	5	102
6	RED=SWITCH/COVER	3	26
7	PINK=MOTOR/WIRING	7	87
8	BLACK=OTHER/CLICKING	1	43
Sheet2