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

--
H. Frank Situmorang

Check this site:

http://office.microsoft.com/en-us/ex...CL100570551033

"Frank Situmorang" wrote:

> 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
>
> --
> H. Frank Situmorang

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?

--
H. Frank Situmorang


"JLGWhiz" wrote:

> Check this site:
>
> http://office.microsoft.com/en-us/ex...CL100570551033
>
> "Frank Situmorang" wrote:
>
> > 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
> >
> > --
> > H. Frank Situmorang

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

On Tue, 27 Nov 2007 20:28:01 -0800, Frank Situmorang <(E-Mail Removed)>
wrote:

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

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

--
H. Frank Situmorang


"Gord Dibben" wrote:

> 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
>
> On Tue, 27 Nov 2007 20:28:01 -0800, Frank Situmorang <(E-Mail Removed)>
> wrote:
>
> >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?
>
>

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

*** Sent via Developersdex http://www.developersdex.com ***