VBA: Count number of words in cell not black

[FryGirl]

In cell G2 I have a sentence; however, a certain number of those words are colored with red font. Not by CF; rather, they are manually colored.

In cell H2 I have a formula to count the total number of words in that sentence. =LEN(G2)-LEN(SUBSTITUTE(G2" ",""))+1

There are a few words in that sentence that are not black. How can I count the non-black cells? Also, this is not just for cell G2. I will be applying this down G2 for a couple of thousand rows.

 

[Eric W]

Try:

Sub CountNonBlack()
Dim r As Long, w As String, ctr As Long, MyLoc As Long

    For r = 2 To Range("G2").End(xlDown).Row
        w = Cells(r, "G").Value
        ctr = 0
        MyLoc = 1
        While MyLoc > 0
            If Cells(r, "G").Characters(Start:=MyLoc, Length:=1).Font.Color <> vbBlack Then ctr = ctr + 1
            MyLoc = InStr(MyLoc, w, " ")
            If MyLoc > 0 Then MyLoc = MyLoc + 1
        Wend
        Cells(r, "H") = ctr
    Next r
End Sub

Book1
	G	H
1
2	This is a test.	1
3	The quick brown fox is red.	3
4	Don't count this.	0
5
Sheet41

XL2BB doesn't capture color changes within a cell, but the words "test", "The", "brown", and "red" were red in my test example.

 

[FryGirl]

Thanks Eric. This works absolutely fantastic!

 

[Eric W]

Happy to help! ? Thanks for the feedback.