Re: count occurences of font color

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

Loading