Tod,

Couple of quick thoughts...

The list of cases should be in order of use with the
most frequent value at the top.

Color all of the cells in the loop range to ColorIndex 3,
before running the loop, and eliminate the last case
from the Select Case statement.

Regards,
Jim Cone
San Francisco, USA


"Tod" wrote in message
...
I've been given a workbook that creates reports and asked
if I can make it run faster. I've managed to get it down
from 1 hour and 40 minutes running time to just under 20
minutes just by cleaning up the code. I imagine they'd be
jumping for joy just at 20 minutes, but I want more!

Part of the code creates a new worksheet, dumps data from
an ADO recordset into the sheet, then color codes a
column of values based on the value in the cell. There
are six different colors that it could be. The only slow
part left of the entire thing is this cycling cells and
coloring based on values. Currently it is something like
this:
For each Cell in mySheet.Range("A2:A" & mySheet.Range
("A65536").End(xlUp).Row
Select Case Cell.Value
Case "ValueA"
Cell.Interior.ColorIndex = 13
Case "ValueB"
Cell.Interior.ColorIndex = 5
Case "ValueC"
Cell.Interior.ColorIndex = 10
Case "ValueD"
Cell.Interior.ColorIndex = 6
Case "ValueE"
Cell.Interior.ColorIndex = 45
Case Else
Cell.Interior.ColorIndex = 3
End Select
Next Cell

Is there a faster way?