Re: count occurences of font color

You could ignore the Null test and just jump out when the first matching
color is found (as I suggested in my earlier answer). It would work in
either case.

ActiveCell.Font.ColorIndex = 41
? ActiveCell.Characters(1,1).Font.ColorIndex
41
--
Regards,
Tom Ogilvy
"keepITcool" <xrrcvgpbby@xxxxxxxxx> wrote in message
news:xn0ei1w8ihfhda000keepitcoolnl@xxxxxxxxxxxxxxxxxxxxx

this will make your processing extremely slow.
as you are working with partially colored text strings.

for partially colored text, the cell's font color will return a NULL
value and you must go thru each character! of the text.

Function CountBlue(MyRange As Range) As Long
CountBlue = ColorCount(MyRange, 41)
End Function
Function CountRed(MyRange As Range) As Long
CountRed = ColorCount(MyRange, 3)
End Function


Function ColorCount(ByVal rRange As Range, ByVal iColor As Long) As Long
Dim rCell As Range
Dim i&, n&

'Reduce the range to "entered text" only
On Error Resume Next
Set rRange = rRange.SpecialCells(xlCellTypeConstants, xlTextValues)
If rRange Is Nothing Then Exit Function
On Error GoTo 0

For Each rCell In rRange.Cells
With rCell
If IsNull(.Font.ColorIndex) Then
'Text Font is partially colored
For i = 1 To Len(.Value)
If .Characters(i, 1).Font.ColorIndex = iColor Then
n = n + 1
Exit For
End If
Next
ElseIf .Font.ColorIndex = iColor Then
'Cell Font is colored
n = n + 1
End If
End With
Next

ColorCount = n

End Function

HTH :)


--
keepITcool
| www.XLsupport.com | keepITcool chello nl | amsterdam


michalaw wrote :

I have a spread*** 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 spread***. I am
fairly certain that something is wrong in the If/Then statement, but
I don't know what. Can anyone help me?


.

Loading