I'm a little bit lost among all these different bits of code...would someone
be willing to sum up for me what the agreed-upon faster code to accomplish
the goal is?

"Tom Ogilvy" wrote:

After further reflection, I agree - you are correct. My bad.

--
Regards,
Tom Ogilvy

"Tom Ogilvy" wrote in message
...
I agree, your check is faster, but I am not seeing those types of
differences.

If we cull it down to the essence of the discussion:

Sub Tester1()
Dim s As Single, e As Single
Dim num As Long, res As Long
Dim b As Boolean
num = 100000
s = Timer
For i = 1 To num
' res = ActiveCell.Characters(1, 1).Font.ColorIndex
Next
Debug.Print Timer - s
s = Timer
For i = 1 To num
' b = IsNull(ActiveCell.Font.ColorIndex)
' res = ActiveCell.Font.ColorIndex
Next
Debug.Print Timer - s
End Sub


I get differences like this

2.191406 ' using characters
1.378906 ' using isnull and not using characters

--
Regards,
Tom Ogilvy


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