Cell.Offset(, ).Interior.ColorIndex Very Slow

[JStellato]

Using this code, the result of the debug.print are between 500 - 600 milliseconds. That's a half a second. I am running a loop of 2000 cells, so that adds up very quickly.

Is there any other way to speed this up? For reference I have set these options first:

    Application.ScreenUpdating = False
    Application.StatusBar = False
    Application.Calculation = xlCalculationManual
    Application.DisplayStatusBar = False
    Application.EnableEvents = False

I'm timing it using the tickcount

Public Declare Function GetTickCount Lib "kernel32.dll" () As Long

t = GetTickCount
    Cell.Offset(, 1).Interior.ColorIndex = 4 ' This is the line that is taking 500ms
Debug.Print GetTickCount - t & " Setting Color Index = 4"

 

[sericom]

What's 'Cell' set to?

 

[Rick Rothstein]

Using this code, the result of the debug.print are between 500 - 600 milliseconds. That's a half a second. I am running a loop of 2000 cells, so that adds up very quickly.

Is there any other way to speed this up?

We cannot tell you if you can speed anything up until we see the code you are using, so... post your current code.

 

[JStellato]

Sorry in my haste I forgot to copy that:

Dim dataRange As Range
Set dataRange = Range(Cells(1, Col), Cells(LastRow1, Col))

For Each Cell In dataRange
    t = GetTickCount
    Cell.Offset(, 1).Interior.ColorIndex = 4 ' This is the line that is taking 500ms
    Debug.Print GetTickCount - t & " Setting Color Index = 4"
Next Cell

 

[JStellato]

I couldn't figure out how to edit the original post, I just posted a reply with the loop. Thanks

 

[Rick Rothstein]

Sorry in my haste I forgot to copy that:

Dim dataRange As Range
Set dataRange = Range(Cells(1, Col), Cells(LastRow1, Col))

For Each Cell In dataRange
    t = GetTickCount
    Cell.Offset(, 1).Interior.ColorIndex = 4 ' This is the line that is taking 500ms
    Debug.Print GetTickCount - t & " Setting Color Index = 4"
Next Cell

Well, of course, that is not all your code since you have references to variables Col and LastRow1 but show no assignment to them. However, if your code is representative of what you are actually doing (assigning the same ColorIndex value to all the cells, then you can speed things up dramatically by doing all the assignment all at once (no loop) using this single line of code (it assumes Col and LastRow1 were already assigned values)...

Range(Cells(1, Col), Cells(LastRow1, Col)).ColorIndex = 4

 

[JStellato]

Well, of course, that is not all your code since you have references to variables Col and LastRow1 but show no assignment to them. ]

It is not all of the code, I find when I break the problem down to the simplest form I get the most help.

My fault, as it's late at night and I'm not representing my problem correctly. But no the range option wouldn't work because contained are multiple if statements:

Dim dataRange As Range
Set dataRange = Range(Cells(1, 1), Cells(2000, 1))

For Each Cell In dataRange
    t = GetTickCount

   If foo < 10 then
         Cell.Offset(, 1).Interior.ColorIndex = 4 ' This is the line that is taking 500ms
   end if

   if foo > 10 then
         Cell.Offset(, 1).Interior.ColorIndex = 5 ' This is the line that is taking 500ms
   end if
    Debug.Print GetTickCount - t & " Setting Color Index "
Next Cell

Hopefully this clarifies it. Thanks for your help.