How to count in visible filtered selection, but not a specific color.

[toondichter]

Hello.

For a reasonably large sheet, I needed to count filtered data. Say for example I needed the amount we encounter the word HELLO in the filtered data.
On this site I've found code to count; and it works like a charm! It works so well, but I must admit I do not understand completely what is what.

Function COUNTIFVISIBLE(r As Range, Crit) As Long
Dim s   As String, f As String, sRow As Long
s = r.Address: sRow = r.Row
If Not IsNumeric(Crit) Then Crit = """" & Crit & """"
f = "=sumproduct(subtotal(3,offset(" & s & ",row(" & s & ")-" & _
        sRow & ",,1)),--(" & s & "=" & Crit & "))"
COUNTIFVISIBLE = Evaluate(f)
End Function
Sub test()
MsgBox COUNTIFVISIBLE(Range("BQ2:BQ200"),"Delete")
End Sub

and

WhatToCount = "HELLO"
CountHellos = COUNTIFVISIBLE(range("A2:A" & LR), WhatToCount)

Now the request changed and I need to count all the HELLO except when the text is red (HELLO)

I have been staring at the function for a while, but I have no idea if this can be implented.

Is there someone willing to help and think along with me?

 

[Herakles]

What determines that the text is red?

 

[toondichter]

What determines that the text is red?

They are colored by the user. The sheet itself has text in black, green and red. the Red ones are not to be counted. Those were colored by users.