Code to count cells with specific value AND specific fill color

[Comrade]

Hello,

Using Excel 2010, I am trying count a range of cells that have a specific value AND a specific fill color, but I gather that the only way I can do this is with VBA. Although I am familiar with and comfortable adding pre-written code into my Excel documents, I don't actually know the nuts and bolts of the code itself.

For various reasons I cannot conditionally format the cells, so each value and fill color is inputted individually. The VBA code that I was trying to use is:

Function CountColorValue(CountRange As Range, CountColor As Range, CountValue As Range)
Dim iNumbers As Long
Dim rCell As Range

For Each rCell In CountRange
If rCell.Interior.ColorIndex - CountColor.Interior.ColorIndex Then
If rCell.Value2 = CountValue.Value2 Then
iNumbers = iNumbers + 1
End If
End If
Next rCell

CountColorValue = iNumbers

End Function

What I have is a monthly calendar that has people assigned a shift, and then the fill color is based on their production that day. I want to count the number of times each person had a specific production that month. That is, I want to count the cells that say "Jim" and are also filled yellow.

Can anyone provide some help with this? Thanks!!

 

[JoeMo]

Try this. Indx is the color index for the fill color of interest.

Excel Workbook
	A	B
3	Tom	3
4	****	*
5	Harry	*
6	Tom	*
7	****	*
8	Harry	*
9	Tom	*
10	****	*
11	Harry	*
12	Tom	*
13	****	*
14	Harry	*
15	Tom	*
16	****	*
17	Harry	*
18	Tom	*
19	****	*
20	Harry	*
Sheet7

Function CountColorValue(CountRange As Range, Indx As Long, CountValue As Variant) As Long
Dim rCell As Range
For Each rCell In CountRange
If rCell.Interior.ColorIndex = Indx Then
    If rCell.Value2 = CountValue Then
        CountColorValue = CountColorValue + 1
    End If
End If
Next rCell
End Function

 

[Comrade]

JoeMo,

You are a class act. Thank you for the help, that is exactly what I needed!