• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

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

polarisking

Member
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?
 
Conditional formatting can be resource hungry but this was quite quick (a fraction of a second):
Code:
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
 
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.
 
Code:
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
 
Thanks to both p45cal and chirayu. The results are in . . .

Range is 732,840 rows by 7 columns

My "old" way = 37 sec
Code:
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
Code:
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
Code:
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.
 
for my code - you know you ca do the same way in terms of instead of subrng.borders, with subrng etc
 
Code:
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
 
Back
Top