Formatting in excel vba takes a long time

Question

I have an excel (2010) sheet where I load the data from a database and then format each row based on a particular cell in each row. The code for formatting takes quite a long time. About 4 minutes for around 150 rows and 15 columns. Here is a snippet of the code. Basically it is a loop that checks the value of row_type and accordingly sets font color, background color etc for each row. Is there a better way to do it than using a loop ? Any other improvements I can make if looping is the answer.

 J = 1
 While J <= iNumRows
    row_type = Worksheets("WaterFall").Range("WaterFallHeaders").Offset(J, -1).Cells(1, 1)

    If row_type = "main_row" Then
        With Worksheets("WaterFall").Range("WaterFallHeaders").Offset(J, 0).EntireRow.Font
            .Bold = False
            .ColorIndex = RGB(0, 0, 0)
        End With
        'yellow Description columns
        With Worksheets("WaterFall").Range("WaterFallHeaders").Offset(J, 0).Resize(1, 5).Interior
                .Color = RGB(204, 255, 204)
        End With
        'grey amount columns
        With Worksheets("WaterFall").Range("WaterFallHeaders").Offset(J, 5).Resize(1, dtCount + 2).Interior
            .Color = RGB(217, 217, 217)
        End With

        'blue action columns
        With Worksheets("WaterFall").Range("WaterFallHeaders").Offset(J, 0).Cells(1, 1).Font
            .Bold = True
        End With
        With Worksheets("WaterFall").Range("WaterFallHeaders").Offset(J, 0).Cells(1, 1).Interior
                .Color = RGB(184, 204, 225)
        End With
    Else
        With Worksheets("WaterFall").Range("WaterFallHeaders").Offset(J, 0).Resize(1, colCount - 1).Font
            .Bold = True
            .Color = RGB(51, 51, 255)
        End With
        With Worksheets("WaterFall").Range("WaterFallHeaders").Offset(J, 0).Resize(1, colCount - 1).Interior
                .Color = RGB(255, 255, 204)
        End With
    End If

    J = J + 1



Wend

Thanks...

Answer 1

This does not look like something that should take 4 minutes to complete (e.g., Range.Autofill for ~100,000 rows takes approx 90 seconds on my machine).

However, you can probably make the code more efficient by reducing the number of calls to certain objects, incorporating better use of With blocks.

This reduces the number of times that the compiler has to refer to Worksheets("WaterFall").Range("WaterFallHeaders") to 1. Previously, you refer to that either 3 or 6 times, depending on which side of the If/Else.

Untested because I don't have your workbook file to test it on. Let me know if this helps or if it gives you any problems.

J = 1
With Worksheets("WaterFall").Range("WaterFallHeaders")
    While J <= iNumRows
        row_type = .Offset(J, -1).Cells(1, 1)

        If row_type = "main_row" Then
            With .Offset(J, 0).EntireRow.Font
                .Bold = False
                .ColorIndex = RGB(0, 0, 0)
            End With

            'yellow Description columns
            With .Offset(J, 0).Resize(1, 5).Interior
                .Color = RGB(204, 255, 204)
            End With

            'grey amount columns
            With .Offset(J, 5).Resize(1, dtCount + 2).Interior
                .Color = RGB(217, 217, 217)
            End With

            'blue action columns
            With .Offset(J, 0).Cells(1, 1)
                .Font.Bold = True
                .Interior.Color = RGB(184, 204, 225)
            End With
        Else

            With .Offset(J, 0).Resize(1, colCount - 1)
                With .Font
                    .Bold = True
                    .Color = RGB(51, 51, 255)
                End With
                .Interior.Color = RGB(255, 255, 204)
            End With
        End If
    Wend
End With