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
