Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Alternate 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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Alternate row shading
You could solve this by recording a macro the next time you manually
do this. You could then assign the macro to a button. Nozza wrote: 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Alternate row shading
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 "Nozza" wrote: 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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Alternate row shading
In article
, 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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Alternate row shading
In article , Mike
H said... 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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Alternate row shading
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 -- Gary "Nozza" wrote in message ... 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 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Alternate row shading
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 On Thu, 22 May 2008 06:18:22 +0100, Nozza wrote: In article , Mike H said... 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 |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Alternate row shading
In article , Gord Dibben
said... 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 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 |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
Alternate row shading
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 On Fri, 23 May 2008 17:19:46 +0100, Nozza wrote: In article , Gord Dibben said... 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 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
alternate row shading kept with autofilter | Excel Discussion (Misc queries) | |||
Alternate Row Shading (Visible Rows Only) | Excel Worksheet Functions | |||
How do I set up alternate row shading with Excel 2003? | Excel Discussion (Misc queries) | |||
Alternate shading colors | Excel Discussion (Misc queries) | |||
Alternate Shading | Excel Discussion (Misc queries) |