Alternating color row makes excel run very slow

  • Thread starter Thread starter Frank Situmorang
  • Start date Start date
F

Frank Situmorang

Hellos,

I have worksheet which contains data almost 35 000 rows. To facilitate easy
reading I want to make alternating color rows. But his will affect vefry
solow in performance.

Can anyone help me how can we make alternating rows, but still running normal?

Thanks for any idea provided.

Frank
 
Thanks JLGWhiz for your help. When I filter it it won't have alternating
color again, could you tell me how can we make it?
 
Frank

In CF>Formula is: enter the following.

=MOD(SUBTOTAL(3,$A1:$A$2),2)=0 Format to a nice color.

This will retain the row formatting throughout sorting or filtering.


Gord Dibben MS Excel MVP
 
Gord:

My problem is when i create this alternating color, the program is running
very slow when I perform filtering for 35000 rows

Any idea to make it faster??
 
Hi Frank,
I use the code below very succesfully.

Sub ColorWhenValueChange()
'
'The xlsweetspot guy would would do it this way.
Dim strActiveAddress As String 'Use this to track single cell
position
Application.ScreenUpdating = False
ActiveCell.Offset(2, 0).Select
Do While Len(ActiveCell.Value) <> 0 ' This will run until a row
isblank.
strActiveAddress = ActiveCell.Address
' Compare the values in the current cell with the one above
If ActiveCell.Value = ActiveCell.Offset(-1, 0).Value Then
ActiveCell.EntireRow.Offset(-1, 0).Copy
ActiveCell.EntireRow.PasteSpecial xlPasteFormats
Else
If ActiveCell.Offset(-1, 0).Interior.ColorIndex = xlNone Then
ActiveCell.EntireRow.Interior.Color = RGB(255, 255, 153) '36
< - Change colour value here
Else
ActiveCell.EntireRow.Interior.ColorIndex = xlNone
End If
End If
Range(strActiveAddress).Select
ActiveCell.Offset(1, 0).Select
Loop
End sub

Best regards,

Les Stout
 
Back
Top