If you are coloring characters within the text string within the cell, then
you have to examine the characters as you have stated. Your function
doesn't do this.


Function CountBlue(MyRange As Range)
Dim iCount As Long, i as Long
Application.Volatile
iCount = 0
For Each Cell In MyRange
for i = 1 to len(cell.value)
If Cell.Characters(i,1).Font.ColorIndex = 41 Then
iCount = iCount + 1
exit for
End If
Next i
Next Cell
CountBlue = iCount
End Function

Make sure the color you are using for blue is actually ColorIndex 41
Assumes that you won't have a cell that has "My dog is a big dog" with
two blue dogs that need to be counted separately.

--
Regards,
Tom Ogilvy


"michalaw" wrote in message
...
I have a spreadsheet of coded survey responses. The code(s) applied to
each
response are indicated by changing the font color of specific words in the
text. For example, the response "I like dogs and cats" would have "dogs"
colored red to indicate that it is in the Dogs category, and "cats"
colored
blue to indicate that it is in the Cats category.
I'm trying to write a macro that produces a count of how many times each
font color occurs in a range of cells by examining the characters in each
response. The code I have developed thus far is this:

Function CountBlue(MyRange As Range)
Dim iCount As Integer
Application.Volatile
iCount = 0
For Each Cell In MyRange
If Cell.Font.ColorIndex = 41 Then
iCount = iCount + 1
End If
Next Cell
CountBlue = iCount
End Function

However, it returns a 0 when I try to use it in my spreadsheet. I am
fairly
certain that something is wrong in the If/Then statement, but I don't know
what. Can anyone help me?