would something like this work? Option Explicit '-----------------------------------------------------------------------------------------------------
Sub hightlight_all() Dim I, stat For I = 1 To Sheets.Count Sheets(I).Select stat = Highlight_On_set(Range(Sheets(I).Name & "Data")) Next I End Sub '----------------------------------------------------------------------------------------------------- Function Highlight_On_set(DataRange As Range) Dim rownm '------------------------------------------------------------------------------------------------ Application.StatusBar = False Application.StatusBar = "Turn ON Highlighting" '------------------------------------------------------------------------------------------------ '=========================================================================== ' Remove all highlighting '=========================================================================== DataRange.Interior.ColorIndex = xlNone DataRange.Font.ColorIndex = 0 DataRange.Font.Bold = False '=========================================================================== ' Highlight Rows '=========================================================================== For Each rownm In DataRange.Rows If (rownm.Row Mod 2 = 0) Then '(checks to see if the row number is even) With Range(Cells(rownm.Row, DataRange.Column), Cells(rownm.Row, DataRange.Column + DataRange.Columns.Count - 1)).Interior .ColorIndex = 37 .Pattern = xlSolid End With End If Next rownm '=========================================================================== '-------------------------------------------------------------------------------------------- Application.StatusBar = False '-------------------------------------------------------------------------------------------- End Function Paul ________________________________ From: Kurt <heislerk...@gmail.com> To: MS EXCEL AND VBA MACROS <excel-macros@googlegroups.com> Sent: Thu, September 9, 2010 12:11:26 PM Subject: Re: $$Excel-Macros$$ Macro to reshade every other row after column sort I don't think the conditional formatting option will work, because the size of the range changes depending on the data. In other words, there is no fixed block to select to apply the conditional formula. On Sep 9, 12:35 am, Dave Bonallack <davebonall...@hotmail.com> wrote: > 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 athttp://www.excel-macros.blogspot.com > > 4. Learn VBA Macros athttp://www.quickvba.blogspot.com > > 5. Excel Tips and Tricks athttp://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 athttp://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 -- ---------------------------------------------------------------------------------- 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
