Hi, I assume your shading is currently done manualy.
It could easily done with VBA or even more easily with Conditional formatting, as long as you aren't currently using it for something else. Select all cells in the block Open the conditional formatting dialogue box Select 'Formula is' Enter: =MOD(ROW(),2)=0 (to shade even rows) or =MOD(ROW(),2)=1 (to shade odd rows) Set the shading format you want OK Hope this helps. Regards - Dave. > Date: Wed, 8 Sep 2010 13:56:09 -0700 > Subject: $$Excel-Macros$$ Macro to reshade every other row after column sort > From: heislerk...@gmail.com > To: excel-macros@googlegroups.com > > I have four worksheets (WS1, WS2, etc.), and on each sheet a dynamic > name ranged (WS1Data, WS2Data, etc.). These named ranges always begin > on B8 but the columns and rows they span will vary based on the data > (so I can't use an absolute reference). The rows in the range are > alternatively shaded. > > Each column (for each range) has an invisible rectangle that, when > clicked, sorts the rows in ascending or descending order, based on the > values in the column. I did this using the "Sort Data with Invisible > Rectangles" method (http://www.contextures.com/xlSort02.html). > > My problem is that when I sort the rows, the alternate shading gets > all messed up. So I'd like to add a call ("ShadeAlternativeRows") at > the end of the Sort macro to reshade the newly sorted rows. > > The code I have works great for the first range ("WS1Data"), because > it specifically references that range. How can I change this code so > it references the range on the active sheet? > > Thanks. > > Code below. > > ### > > Public Sub ShadeAlternateRows() > > Dim r As Long > > Set curWks = ActiveSheet > With curWks > > ' Automatically select the range to apply alternate shading > With Range("WS1Data") > > ' remove any previous shading > .Interior.ColorIndex = xlColorIndexNone > > ' Shade every second row lt gray (i.e., color index = > 15) > For r = 2 To .Rows.Count Step 2 > .Rows(r).Interior.ColorIndex = 15 > Next r > End With > End With > > End Sub > > ### > > -- > ---------------------------------------------------------------------------------- > Some important links for excel users: > 1. Follow us on TWITTER for tips tricks and links : > http://twitter.com/exceldailytip > 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 > 3. Excel tutorials at http://www.excel-macros.blogspot.com > 4. Learn VBA Macros at http://www.quickvba.blogspot.com > 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com > > To post to this group, send email to excel-macros@googlegroups.com > > <><><><><><><><><><><><><><><><><><><><><><> > HELP US GROW !! > > We reach over 7000 subscribers worldwide and receive many nice notes about > the learning and support from the group.Let friends and co-workers know they > can subscribe to group at > http://groups.google.com/group/excel-macros/subscribe -- ---------------------------------------------------------------------------------- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post to this group, send email to excel-macros@googlegroups.com <><><><><><><><><><><><><><><><><><><><><><> HELP US GROW !! We reach over 7000 subscribers worldwide and receive many nice notes about the learning and support from the group.Let friends and co-workers know they can subscribe to group at http://groups.google.com/group/excel-macros/subscribe
