Is there a fast, efficient way to "zebra" format large number of rows?

[polarisking]

I have a common formatting routine that for a given worksheet computes the last row, loops through each row and depending if ROWNUM MOD 2 is 0 or 1 formats the entire row with alternating colors, aka Zebra formatting.

Never thought this process was that "expensive", performance-wise, until today. My sheet has 800,000+ rows with 7 columns and the formatting process is taking 40sec on a 64bit machine with 64GB RAM and Intel W-2155 @ 3.30Ghz processor (so all the performance drag is on me.)

Any ideas how I can I make the alternating row formatting most efficient?

 

[p45cal]

Conditional formatting can be resource hungry but this was quite quick (a fraction of a second):

Sub blah()
With Range("A1:G800000")
  .Cells.FormatConditions.Delete
  .FormatConditions.Add Type:=xlExpression, Formula1:="=ISEVEN(ROW())"
  With .FormatConditions(1).Interior
    .PatternColorIndex = xlAutomatic
    .ThemeColor = xlThemeColorAccent2
    .TintAndShade = 0.399945066682943
  End With
End With
End Sub

 

[Eloise T]

I have a similar issue, but mine is taking up to 4 minutes!
I just upgrade to a 4-core, 8 thread Intel i7 CPU at 3.6 Ghz (from a 2-core Intel i5 CPU) and bumped the RAM up to 8GB from 5GB and it's still taking over a minute.

 

[chirayu]

Sub Zebra()

Dim Rng As Range 'Set range and loop through it
Dim SubRng As Range 'Loop through the range per row
Dim LstRow As String 'Finds the last row

LstRow = Cells(Rows.Count, "A").End(xlUp).Row 'change A to whatever column that is non blank for the last row count
Set Rng = Range("A2:B" & LstRow) 'change A2 to whatever start column/row you want, change B to whatever end column you want

For Each SubRng In Rng
   
    If SubRng.Row Mod 2 Then
    Else
        SubRng.Interior.Color = RGB(191, 191, 191) 'Change "A" to whatever start column you want
    End If

Next SubRng

End Sub

 

[polarisking]

Thanks to both p45cal and chirayu. The results are in . . .

Range is 732,840 rows by 7 columns

My "old" way = 37 sec

For ctr = FirstRow + 1 To LastRow
        If ctr Mod 2 <> 0 Then
            With Range(Cells(ctr, 1), Cells(ctr, LastCol))
                .Interior.Color = RGB(220, 230, 241)
                .Borders(xlTop).Weight = xlThin
                .Borders(xlBottom).Weight = xlThin
                .Borders(xlBottom).Color = RGB(49, 134, 155)
                .Borders(xlTop).Color = RGB(49, 134, 155)
            End With
        End If
    Next ctr

p45cal's way = 1.5 sec - a 96% reduction in time

With Range(Cells(2, 1), Cells(LastRow, LastCol))
        .Cells.FormatConditions.Delete
        .FormatConditions.Add Type:=xlExpression, Formula1:="=ISODD(ROW())"
        With .FormatConditions(1)
            .Borders(xlTop).Weight = xlThin
            .Borders(xlBottom).Weight = xlThin
            .Borders(xlBottom).Color = RGB(49, 134, 155)
            .Borders(xlTop).Color = RGB(49, 134, 155)
            .Interior.Color = RGB(220, 230, 241)
        End With
    End With

chirayu's way = 232 sec

For Each SubRng In Rng2
        If SubRng.Row Mod 2 = 1 Then
            SubRng.Borders(xlTop).Weight = xlThin
            SubRng.Borders(xlBottom).Weight = xlThin
            SubRng.Borders(xlBottom).Color = RGB(49, 134, 155)
            SubRng.Borders(xlTop).Color = RGB(49, 134, 155)
            SubRng.Interior.Color = RGB(220, 230, 241)
      End If
    Next SubRng

Yet another reason why I love this forum. Thank you all.

 

[chirayu]

for my code - you know you ca do the same way in terms of instead of subrng.borders, with subrng etc

 

[AlanSidman]

Wouldn't converting the range of data to a Table achieve the same result? No VBA required.

 

[GraH - Guido]

@AlanSidman , why not?

 

[polarisking]

[/quote]

for my code - you know you ca do the same way in terms of instead of subrng.borders, with subrng etc

chirayu, I don't understand the modification you're suggesting to your code. Can you elaborate?

 

[chirayu]

For Each SubRng In Rng2
    If SubRng.Row Mod 2 = 1 Then
        With SubRng
            .Borders(xlTop).Weight = xlThin
            .Borders(xlBottom).Weight = xlThin
            .Borders(xlBottom).Color = RGB(49, 134, 155)
            .Borders(xlTop).Color = RGB(49, 134, 155)
            .Interior.Color = RGB(220, 230, 241)
        End With
    End If
Next SubRng