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?

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?

That does it! Thank you so much!
If you don't mind, could you help me understand exactly what 2 of the lines
do?

"for i = 1 to len(cell.value)"
Does this establish i as the length of the text string in the cell?

"If Cell.Characters(i,1).Font.ColorIndex = 41 Then"
I think the (i,1) part tells the function to examine certain characters
within the string - is this correct?
Thanks again for your help!





"Tom Ogilvy" wrote:

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?

it tells i to successively take on the values from 1 to the number of
characters in the string.

then you look at each character as you correctly interpreted

cell.Characters(character position, number of characters)

In your case, number of characters would be 1.

You could also avoid macros and do something like

=Countif(B:B,"*dog*")

as long as dog would appear only once in each cell of interest.

--
Regards,
Tom Ogilvy

"michalaw" wrote in message
...
That does it! Thank you so much!
If you don't mind, could you help me understand exactly what 2 of the
lines
do?

"for i = 1 to len(cell.value)"
Does this establish i as the length of the text string in the cell?

"If Cell.Characters(i,1).Font.ColorIndex = 41 Then"
I think the (i,1) part tells the function to examine certain characters
within the string - is this correct?
Thanks again for your help!





"Tom Ogilvy" wrote:

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?

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 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?

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" wrote in message
. com...

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 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?

Tom,

Not true. The null test is there to avoid the loop
when the cell(not partial text) is formatted.
in a likely scenario most cells will have
CELL.font.colorindex = xlAutomatic

and your code must loop all characters to find out.


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


Tom Ogilvy wrote :

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

Not true. Your isnull test determines the need to loop or not. If it is
null, you loop. No difference there in approaches except the extra test.

If it isn't null, you check the font color of the range. In that case, my
checking the first character would stop the loop just as easily with no
additional looping..

For Each rCell In rRange.Cells
With rCell
For i = 1 To Len(.Value)
If .Characters(i, 1).Font.ColorIndex = iColor Then
n = n + 1
Exit For
End If
End If
End With
Next

Should be just as effective. No looping through xlautomatic except in the
same situations your original code. would. loop.

--
Regards,
Tom Ogilvy

"keepITcool" wrote in message
. com...
Tom,

Not true. The null test is there to avoid the loop
when the cell(not partial text) is formatted.
in a likely scenario most cells will have
CELL.font.colorindex = xlAutomatic

and your code must loop all characters to find out.


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


Tom Ogilvy wrote :

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

Tom,

Please rethink: The difference is in the cells which have NOT been
partially formatted.

For a cell which has an entire blue font there is hardly any difference
as the loop will exit on the first character. (although the isnull is
more efficient (factor 10) as it avoids the characters method.)

However:
if a cell is (entirely) formatted as Auto or Red (not blue) then your
code must complete the loop to determine that all
..Characters(i,1).font.colorindex < 41

Run a test:
1000 rows with text of 30 chars.

..cells.font.colorindex= 41
my code: .05 secs.
your code: .66 secs.


..cells.font.colorindex=xlAutomatic or vbRed
my code: .05 secs.
your code: 20.00 secs




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


Tom Ogilvy wrote :

Not true. Your isnull test determines the need to loop or not. If
it is null, you loop. No difference there in approaches except the
extra test.

If it isn't null, you check the font color of the range. In that
case, my checking the first character would stop the loop just as
easily with no additional looping..

For Each rCell In rRange.Cells
With rCell
For i = 1 To Len(.Value)
If .Characters(i, 1).Font.ColorIndex = iColor Then
n = n + 1
Exit For
End If
End If
End With
Next

Should be just as effective. No looping through xlautomatic except
in the same situations your original code. would. loop.