How to speed up loop

[kublakhan]

The following code works, albeit slowly. Any tips on speeding it up? I'm guessing there must be a way to establish a smaller, discontiguous range that only includes cells that contain data and/or have been formatted.
[vba]Sub ColorLoop()

Dim cel As Range
Dim myWorksheet As Range

Set myWorksheet = Range("A1:IV65536")

For Each cel In myWorksheet
If cel.Font.ColorIndex = 5 Then
cel.Interior.ColorIndex = 36
End If
Next cel

End Sub
[/vba]

[rory]

You could change this:
Set myWorksheet = Range("A1:IV65536")
to this:
Set myWorksheet = ActiveSheet.UsedRange

Regards,
Rory

[kublakhan]

Works like a charm. Thanks for introducing me to UsedRange--it will come in handy.

[rory]

You're welcome. You should just be aware that UsedRange is not always what you think it ought to be (Excel sometimes stubbornly remembers ranges that have had formatting applied as still being 'used') but for these purposes it is better than specifying every cell on the worksheet!

[mdmackillop]

Also check out SpecialCells which you can use to ignore blanks etc.

[Bob Phillips]

You're welcome. You should just be aware that UsedRange is not always what you think it ought to be (Excel sometimes stubbornly remembers ranges that have had formatting applied as still being 'used') but for these purposes it is better than specifying every cell on the worksheet!

But easily sorted http://www.contextures.com/xlfaqApp.html#Unused