Code to count cells with specific value AND specific fill color

Comrade

New Member
Joined
Dec 3, 2016
Messages
2
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!!
 

Excel Facts

How to fill five years of quarters?
Type 1Q-2023 in a cell. Grab the fill handle and drag down or right. After 4Q-2023, Excel will jump to 1Q-2024. Dash can be any character.

JoeMo

MrExcel MVP
Joined
May 26, 2009
Messages
17,433
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
Try this. Indx is the color index for the fill color of interest.
Excel Workbook
AB
3Tom3
4*****
5Harry*
6Tom*
7*****
8Harry*
9Tom*
10*****
11Harry*
12Tom*
13*****
14Harry*
15Tom*
16*****
17Harry*
18Tom*
19*****
20Harry*
Sheet7


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

New Member
Joined
Dec 3, 2016
Messages
2
JoeMo,

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

Watch MrExcel Video

Forum statistics

Threads
1,130,112
Messages
5,640,168
Members
417,129
Latest member
geekzilla

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top