 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
| HomeDiscussionsAccessExcelInfoPathOutlookPowerPointPublisherWordDirectoryUser Groups Related Topics Outlook ExpressInternet ExplorerWindowsMS Server ProductsMore Topics ... |
MS Office Forum / Excel / Programming / May 2008Alternate row shading
I use some wide spreadsheets which get printed out landscape on A3 paper in order to track student progress in multiple subject areas. Each row is for an individual student. At the minute I highlight alternate rows (ctrl-clicking) and then set a slightly shaded background, as it makes reading across a sheet a lot easier than simply printing the gridlines. (Kinda takes me back to the good old days when computer printouts could come on that green and white paper...) What I want is a macro to do this for me. So I simply click on the first row header of the range, and then shift click on the last in the range, and then I want to be able to select what background, and then the macro takes over and sets each alternate row to have the selected background. I could then assign the button to a toolbar.... So, any thoughts on how this might be achieve? Noz You could solve this by recording a macro the next time you manually do this. You could then assign the macro to a button. > I use some wide spreadsheets which get printed out landscape on A3 > paper in order to track student progress in multiple subject areas. [quoted text clipped - 18 lines] > > Noz In article <f9cf9140-dd7b-4701-9e4b-cd113d321c89@m44g2000hsc.googlegroups.com>, KLZA said... >You could solve this by recording a macro the next time you manually >do this. You could then assign the macro to a button. Tried this, but the range changes with each sheet, and I haven't been able to work out how to do the alternate row bit in a macro. Thanks for the reply Noz Hi, You could do it with a macro but by far the simplest is to select your range then Format|Conditional format | Formula is Paste in this formula =MOD(ROW(),2)=0 select a colour and click OK Mike > I use some wide spreadsheets which get printed out landscape on A3 > paper in order to track student progress in multiple subject areas. [quoted text clipped - 18 lines] > > Noz >Hi, > >You could do it with a macro but by far the simplest is to select your range >then When I see a phrase like "by far the simplest..." I usually think "Here we go again" ;) >Format|Conditional format | Formula is >Paste in this formula >=MOD(ROW(),2)=0 >select a colour and click OK Good Grief! That really is simple - *and* for once I understand why it works! This ones a keeper. Thanks for the reply Noz Noz =MOD(SUBTOTAL(3,$A1:$A$2),2)=0 This one will retain the banding when you filter the data. =MOD(ROW(),2)=0 will not retain the banding through filtering. Gord Dibben MS Excel MVP >>Hi, >> [quoted text clipped - 18 lines] > >Noz >Noz > [quoted text clipped - 5 lines] > >Gord Dibben MS Excel MVP Gord Thanks for posting this - I have used it - and as you say it works even when filtering. Brilliant! But my question now is how does this work? I understand the MOD, but what is the subtotal bit actually doing? I know that the 3 is the function reference number for COUNTA, which does a count if present... Just curious that's all - as I like to know why something that works, works! :) Noz Haven't figured that one out myself. Copied from Debra's site and use it all the time but I'm thick as a post when it comes to formulas. Hang in there. Biff or someone will explain it to us.. Gord >>Noz >> [quoted text clipped - 21 lines] > >Noz here's some code that will do it. Sub banding() Dim ws As Worksheet Dim rng As Range Dim y As Long Dim lastrow As Long Dim lastcol As Long Set ws = Worksheets("Sheet1") lastrow = ws.Cells(Rows.Count, "A").End(xlUp).Row lastcol = ws.Cells(1, Columns.Count).End(xlToLeft).Column Set rng = ws.Range(Cells(1, "A"), Cells(lastrow, lastcol)) With rng For y = 2 To rng.Rows.Count If y Mod 2 = 0 Then Range(.Cells(y, "A"), .Cells(y, lastcol)).Interior.ColorIndex = 35 Else Range(.Cells(y, "A"), .Cells(y, lastcol)).Interior.ColorIndex = 0 End If Next End With End Sub  SignatureGary >I use some wide spreadsheets which get printed out landscape on A3 > paper in order to track student progress in multiple subject areas. [quoted text clipped - 18 lines] > > Noz |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.