Alternate row shading

[Nozza]

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

 

[KLZA]

You could solve this by recording a macro the next time you manually
do this. You could then assign the macro to a button.

 

[Mike H]

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]

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

 

[Nozza]

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

 

[Gary Keramidas]

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

 

[Gord Dibben]

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

 

[Nozza]

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

 

[Gord Dibben]

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