Conditionally color format each cell in a range with VBA

[Rx_]

Does anyone have any improvemnets for speed to suggest?
Some of my Excel reports are 20,000 rows (with autofilter and other features for users to conduct analysis)

It seems that I remember a Select Case is suppose to run much faster.
And, perhaps there is more efficient and faster code?

Background:
Createing a user form that lets users design SQL used to run a query (not shown) to place in a recordset. After using CopyFromRecordset to move the date to Excel:
The following code shows how to select a range (one column in this case) and to format each cell based on the value in the cell.

Dim [FONT=Calibri]Spanish_Inquisition[/FONT]                 As Excel.Range
' The column header is on row 4, data starts on line 5 (thus the offset)
' Not shown - when CopyFromRecordset used to take data from Access
       ' to Excel - save the number of records in variable
 intMaxRecordCount
' ObjXL is an object variable to create an instance of Excel
 
Set [FONT=Calibri][FONT=Calibri]Spanish_Inquisition[/FONT][/FONT]= objXL.Range("K5:K" & (intMaxRecordCount + 5))
    For Each Cell In [FONT=Calibri]Spanish_Inquisition[/FONT]
        ' perhaps a Select Case would be better - anyone care to try?
        If Cell.Value = "State" Then
            Cell.Interior.ColorIndex = 3
        End If
        If Cell.Value = "Federal" Then
            Cell.Interior.ColorIndex = 4
        End If
        If Cell.Value = "County" Then
            Cell.Interior.ColorIndex = 18        
        End If
 
        If Cell.Value <> "State" And Cell.Value <> "Federal" And Cell.Value <> "County" And Cell.Value <> "Miscellaneous" Then
            Cell.Interior.ColorIndex = xlNone
        End If
 
    Next
  Set [FONT=Calibri]Spanish_Inquisition[/FONT]= Nothing

 

[ajetrumpet]

if the case statement works better, then how about something like:

select case cell.value
case = "whatever"
cell.interior = "whatevercolor"
case = "whatever2"
etc, etc, etc
ETC, ETC....
end select


continuous if statements consolidated is simply a case statement, that's probably why it's faster. there is no consolidation for a range looping through cells though unfortunately

 

[Brianwarnock]

You should atleast use Elseif statements instead of a fresh If everytime which means that even after a hit you still process all of the other Ifs

Brian

 

[pbaldy]

Select/Case would likely be faster, but I would look at Conditional Formatting. You could either put it in beforehand if you're using a template, or after using FormatConditions. More info in VBA help.

 

[boblarson]

Why use VBA? You only have 3 conditions and something if it doesn't meet those conditions, which means you can use regular conditional formatting (if you get on Excel 2010 you have like 32 conditions you can use ).

 

[Brianwarnock]

DAMN

I assumed that it was example code rather than the complete set, should have thought a bit more before replying as well as reading more carefully.

Brian

 

[Rx_]

Thanks Heaps!
I will take that into consideration before finalizing the code.
Amazing, my CopyFromRecordset statement is taking (according to my time stamp in code) about 30 times longer to complete than this code takes with an IF statement.
Just showed 3 as example, actual number is 12.

Office 2010 -- really looking forward to this! Buying the Server OS to put Share Point and SQL Server (should I get a portable or a home server?) but that is another topic alltogether.

And nobody expects the Spanish_Inquisition?

 

[chergh]

have you tried turning screenupdating off? It is the first thing to do whenever your code takes anytime to run.

 

[Rx_]

That is a good point.

When creating an Excel object instance (ref: ObjXL above)
the visible (ObjXL.Visible = False) is set to off.
I have yet to do that in Test Mode:
As you say, the speed increases, and you don't have a pesky user attempting to click within the cells during the automated construction.

I am finisheing the report today of tomorrow and posting it on a Production Windows 2003 Server that uses Citrix to distribute it to users.
My development / Test Mode is on an old XP Desktop with 3 GB of RAM (it is what the client provides).

My Excel report uses vba code to start a timer from the button click to the Excel report completion to posts the Time to Create: xxx.x Seconds in Cell A1, A2
This enables users alert the IT staff should things not be working efficiently.

I will try to remember to come back and post the difference. It could just be the Virutal Memory and Disk thrashning causing this.