Sorting w/alternate bars

Skip to first unread message

Aug 25, 2001, 4:09:47 PM8/25/01
I have an excel 5.0 spreadsheet that has alternating rows of shaded
cells to aid in following the lines across on a large printout.
(Every other row is lightly shaded) The problem is, when I go to sort
the data that has been entered, the row shading stays with the data
and messes up the alternating row theme. Is there a way to sort the
only the data without affecting the cell shade formating?
Thanks in advance,

-----= Posted via Newsfeeds.Com, Uncensored Usenet News =----- - The #1 Newsgroup Service in the World!
-----== Over 80,000 Newsgroups - 16 Different Servers! =-----

David McRitchie

Aug 25, 2001, 6:54:38 PM8/25/01
Hi Mark,

Since you already had interior colors, then you probably
already have borders because interior color wipes out gridlines.
For help in picking a different color see my

Here is the a solution posted by Dana
I think you will want to include this near the beginning to
clear out existing color.
Cells.Interior.ColorIndex = xlNone

Use the following macro to reset color stripes after sorting.

From: Dana De (
Subject: Re: Coloring Alternate Rows in Excel
Newsgroups: microsoft.public.excel.misc
View: Complete Thread (3 articles) | Original Format
Date: 1998/01/17

Here is something I use to shade every Even Row.
If you do not like any of the 56 colors available for use, then go to
<Options> <Color> and modify one of the colors. Then change the macro to
refer to this (the ColorIndex number). The best way to get the index number
is to just record a short macro and select the color. Then look at the
macro to
get the color index. Modify the ColorIndex number as appropriate.
(Modifying 1 of the colors here affects the whole workbook I believe.)

Sub Alternate_Row_Shading()
' Shades alternate rows with data
Dim R As Long 'Row Ref
Dim LastRow As Long
Dim Tbl
Set Tbl = Range(Cells(1, 1),
LastRow = Tbl.Rows.Count
' Shade even rows

For R = 2 To LastRow Step 2
With Tbl.Rows(R).Interior
.ColorIndex = 15
.Pattern = xlSolid
End With
' Do not shade odd row above
Tbl.Rows(R - 1).Interior.ColorIndex = xlNone

' Need to Check last row
If LastRow Mod 2 Then Tbl.Rows(LastRow).Interior.ColorIndex = xlNone
End Sub

<> wrote in message

Reply all
Reply to author
0 new messages