Thread: Format Every Nth Row

I have created a VBA application to handle some of our reporting needs. I would like to improve the amount of time the report takes to manipulate data and place it back into an Excel worksheet. I can do this pretty quickly (0.06 seconds) when only putting the data into the sheet. However, if I add the needed formatting to the report, it adds an aditional second to the run time. I was able to correct some of this issue by writing the format all at once to a range, rather then applying it one row at a time, however, with this method I can only use one format for a row.

What I need to do is apply one specific format to the first two rows then on the third row a different format, then repeat this pattern through the required range.

I currently am writing this format to then entire range, then using a loop to update every third row with the needed format.

Is there a better way to do this?

Code:

Private Sub AddSomeFormatting(ByRef curSheet, lastRow As Long)
 With curSheet
        With .Range(curSheet.Cells(7, 3), curSheet.Cells(lastRow, 12))
            .Style = "Comma"
            .NumberFormat = "_(* #,##0_);_(* (#,##0);_(* ""-""??_);_(@_)"
        End With
       'This overides the formatting in the revenue columns with currency instead of comma style
        With .Range(curSheet.Cells(7, 5), curSheet.Cells(lastRow, 6))
            .Style = "Currency"
            .NumberFormat = "_($* #,##0_);_($* (#,##0);_($* ""-""??_);_(@_)"
        End With
        With .Range(curSheet.Cells(7, 13), curSheet.Cells(lastRow, 18))
            .Style = "Currency"
        End With
    End With
End Sub

Code:

Private Sub AddDiffPercentFormulas(ByRef curSheet, ByRef data, ByVal rowNumber)
    With curSheet.Cells(rowNumber, 3).Resize(1, 16)
        .value = data
        .NumberFormat = "0.00%"
         With .Interior
            .Pattern = xlSolid
            .PatternColorIndex = xlAutomatic
            .ThemeColor = xlThemeColorAccent3
            .TintAndShade = 0.799981688894314
            .PatternTintAndShade = 0
        End With
        With .Borders(xlEdgeBottom)
            .LineStyle = xlContinuous
            .ColorIndex = 0
            .TintAndShade = 0
            .Weight = xlThin
        End With
    End With
    
End Sub

Sorry I should have noted all of that. I have the following turned off during these operations.

Code:

 With Application
        .ScreenUpdating = False
        .Calculation = xlCalculationManual
        .DisplayStatusBar = False
        .EnableEvents = False
    End With