Counting non-empty cells with a set range of values of a certain font colour

[NZWardy]

Hi,
I have been going around in circles trying to get this to work as only have a mild understanding of VBA. I have a large spreadsheet for our staff work allocation and when they are on jobs/training or spare they are in a specific font colour.
Each column is a separate day and at the bottom I want to automatically count the number of spare resources which ends up in a graph. I am using =CountColour(DD12:DD101,$A$120) in this cell.

My VBA code is currently this;

Public Function CountColour(pRange1 As Range, pRange2 As Range) As Double
Application.Volatile
Dim rng As Range
Dim stafflist As Range
Dim foundCell As Range
On Error Resume Next
Set stafflist = Sheet31.Range("B2:36")
    For Each rng In pRange1
    If rng <> "" Then
    Set foundCell = stafflist.Find(What:=rng.Value, LookIn:=pRange1, LookAt:=xlPart)
    If foundCell <> "" And rng.Font.Color = pRange2.Font.Color Then
    CountColour = CountColour + 1
    End If
    End If
Next
End Function

What am I missing?

 

[Samael2021]

I am not sure about what you ask for and let me share this one. For the range, using
"If Not rng Is Nothing Then" "If Not foundCell is Nothing Then" is better than "If rng <> "" Then"

 

[NZWardy]

I am not sure about what you ask for and let me share this one. For the range, using
"If Not rng Is Nothing Then" "If Not foundCell is Nothing Then" is better than "If rng <> "" Then"

Ok, thanks for that, have edited it to now read;

Public Function CountColour(pRange1 As Range, pRange2 As Range) As Double
Application.Volatile
Dim rng As Range
Dim stafflist As Range
Dim foundCell As Range
On Error Resume Next
Set stafflist = Sheet31.Range("B2:37")  
    For Each rng In pRange1
    If Not rng Is Nothing Then
    Set foundCell = stafflist.Find(What:=rng.Value, LookIn:=pRange1, LookAt:=xlPart)
    If Not foundCell Is Nothing Then
    If rng.Font.Color = pRange2.Font.Color Then
    CountColour = CountColour + 1
    End If
    End If
    End If
Next
End Function

Still doesn't count the spare resource though. Every cell is resulting in 0.
Here are 2 pics of the main sheet and the reference range