Count Cells Based on Cell Color

[lichldo]

I found the following code online to be able to count how many cells are in a certain color, however its only returning #NAME

So I'm using this VBA -
Function GetColorCount(CountRange As Range, CountColor As Range, TheValue As Variant)
Dim CountColorValue As Long
Dim TotalCount As Long
Dim rCell As Range
CountColorValue = CountColor.Interior.Color
Set rCell = CountRange
For Each rCell In CountRange
If rCell.Interior.Color = CountColorValue And rCell.Value = TheValue Then
TotalCount = TotalCount + 1
End If
Next rCell
GetColorCount = TotalCount
End Function

And then in the cell I want the sum total in, I have =GetColorCount($B$2:$B$25,L1)
Where the range I want to total is B2:B25 and L1 has the background color I want to count

This is not working

 

[Joe4]

Where have you placed this VBA code?
You should place it in a new, General Module in your worbook, and NOT put it in the "ThisWorkbook" or one of the "Sheet" modules.

This link show you how to create a new module if you do not know how to do that: Insert and run VBA macros in Excel - step-by-step guide - Ablebits.com

 

[lichldo]

Where have you placed this VBA code?
You should place it in a new, General Module in your worbook, and NOT put it in the "ThisWorkbook" or one of the "Sheet" modules.

This link show you how to create a new module if you do not know how to do that: Insert and run VBA macros in Excel - step-by-step guide - Ablebits.com

Thanks, I did not have it in the correct place but I have now done that.

However now it gives me the error code #VALUE!

 

[Joe4]

The code you are using has 3 arguments:
Function GetColorCount(CountRange As Range, CountColor As Range, TheValue As Variant)

and your formula only has two:
=GetColorCount($B$2:$B$25,L1)

So you do not seem to be using the formula as created.
I do not think you are using the right function for what you want to do.

Did you get this function from here: https://answers.microsoft.com/en-us...criteria/12819f9b-4666-40d6-9536-b16ee610b76f
Note what it says in the original question:
Following instructions on how to create a UDF to count colors worked perfectly but I also would like it to count the colors according to specific text in the cell.

If you are only wanting to count based on color with no other conditions, then you do not want to use that version.
Instead, you should be using the original version in that question:

Function GetColorCount(CountRange As Range, CountColor As Range)
Dim CountColorValue As Integer
Dim TotalCount As Integer
CountColorValue = CountColor.Interior.ColorIndex
Set rCell = CountRange
For Each rCell In CountRange
  If rCell.Interior.ColorIndex = CountColorValue Then
    TotalCount = TotalCount + 1
  End If
Next rCell
GetColorCount = TotalCount
End Function