Alternating color row makes excel run very slow

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Frank Situmorang - 28 Nov 2007 02:04 GMT

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

Signature

H. Frank Situmorang

Reply to this Message

JLGWhiz - 28 Nov 2007 02:49 GMT

Check this site:

http://office.microsoft.com/en-us/excel/HA011373371033.aspx?pid=CL100570551033

> Hellos,
>
[quoted text clipped - 7 lines]
>
> Frank

Reply to this Message

Frank Situmorang - 28 Nov 2007 04:28 GMT

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?

Signature

H. Frank Situmorang

> Check this site:
>
[quoted text clipped - 11 lines]
> >
> > Frank

Reply to this Message

Gord Dibben - 28 Nov 2007 05:38 GMT

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

>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?

Reply to this Message

Frank Situmorang - 28 Nov 2007 06:55 GMT

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??

Signature

H. Frank Situmorang

> Frank
>
[quoted text clipped - 8 lines]
> >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?

Reply to this Message

Les Stout - 28 Nov 2007 09:40 GMT

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

Reply to this Message