Hi Tod,

I believe the following methodology would run a lot quicker.

1. Add a temporary column alongside your existing column(s). Use AutoFill
(or an array) to populate that column with numbers from 1 to
NumberOfRows.

2. Sort on Column A to arrange all ValueA's & ValueB's etc into contiguous
blocks.

3. Use Find (and logic) to locate the first & last cells of each ValueX
block.

4. Format each block using syntax like Range(FirstCell,LastCell).Interior...

5. Resort on the temporary column to get your rows back into original order,
then delete the temp column.


Regards,
Vic Eldridge



"Tod" wrote:

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?