Counting cells with a certain color and value

[garretthered]

I need help trying to get a count of cells that have a specific fill color and certain value. It would be like how many pink cells contain the value 3.

 

[garretthered]

I tested that change and the code doesn't seem to be working correctly.
When I call the the function: =CountColorValue(B1:B8)
it returns #VALUE!

 

[Joe4]

The CountColorValue you posted is not a Function, it is a Sub Procedure.
You cannot call Sub Procedure like you do a function, you simply run it from the Macro Menu (or attach the code to a button or keyboard shortcut).

 

[garretthered]

My code seems to a flaw in it
When use it returns #VALUE!. So i either messed up a part of the code am using it incorrectly.

 

[garretthered]

I changed it from sub to Function

Function CountColorValue()


Dim numbers As Long, lastrow As Long
Dim rng As Range, c As Range


'Find the last row
lastrow = Range("B" & Rows.Count).End(xlUp).Row
'Set the range you want to search through
Set rng = Range("B1:B" & lastrow)


'Iterate through each cell in the range
For Each c In rng
'If the interior color is 2 (standard pink), not blank and not a number
If c.Interior.ColorIndex = "22" And c.Value = 3 Then '<> "" And Not IsNumeric(c.Value) Then
'Add 1 to numbers
numbers = numbers + 1
End If
Next c


'Message box with the value of numbers, change to display however you'd like
'MsgBox numbers


End Function

 

[Joe4]

Let's take a step back here and make sure we define all the requirements.

What can you tell me about the range you want to run this on?
Will it be the same column every time, or does it need to be dynamic?
Where do you want the results returned to, a specific cell, a message box, a VBA variable?

 

[garretthered]

The count would run for a single column and give the value underneath that column. It would also need to be dynamic, because it will look for a two different colors and at time multiple values (pink - female, cyan - male, shift - 12, 23, 1, 2, 3).