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:
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!!
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!!