One of us must be going blind Ken :-)

This is on that link ...


It is also possible to stripe every 3rd row, or every 4th row, etc. This is
achieved by changing the value of the divisor argument to the 'MOD'
function. So
=MOD(ROW(),3)=0 will stripe every 3rd row
=MOD(ROW(),4)=0 will stripe every 4th row
etc.


Similarly, it is simple to change which row the striping starts at. In this
instance, the value that the 'MOD' function is being compared against is
changed. The value tested against can be any value between 0 and the divisor
minus one, as these are the only values that the 'MOD' function will return.
Examples a
=MOD(ROW(),2)=1 will stripe every 2nd row, starting at row 1
=MOD(ROW(),3)=1 will stripe every 3rd row, starting at row 1
=MOD(ROW(),3)=2 will stripe every 3rd row, starting at row 2
=MOD(ROW(),4)=1 will stripe every 4th row, starting at row 1
=MOD(ROW(),4)=2 will stripe every 4th row, starting at row 2
=MOD(ROW(),4)=3 will stripe every 4th row, starting at row 3
etc.

The general formula is
=MOD(ROW(),m+1)=MOD(n,m+1)
where m is the number of non-coloured (blank) rows between coloured rows,
and n is the start row.

<<<<<<<<<<<<<<<<<<<

Seems to cover all eventualities to me

Regards

Bob


"Ken Wright" wrote in message
...
Hi Bob - Just trawling through your link, and I may be going blind but
couldn't see any mention for increasing the number of rows within the
colour
banding. I usually chuck out the note below for these questions, so
wondered if you might want to add a couple of examples similar to the ones
at the end of my note:-


Use a conditional format to do this and it will not move when you sort the
rows:-

Do CTRL+A, then Format / Conditional Formatting / Change 'cell value is'
to
'formula is' and put
in =MOD(ROW(),2)=1

Now click on the format button and choose a nice pastel colour from the
patterns tab. Hit OK till
you are out and you are done.

You could also put in
=MOD(ROW(),2)=0 if you wanted the other rows to be coloured.

You could also put in
=MOD(ROW(),3)=0 if you wanted every 3rd row to be coloured.

You could also put in
=MOD(ROW(),4)=0 if you wanted every 4th row to be coloured.

You could also put in
=MOD(ROW(),5)=0 if you wanted every 5th row to be coloured.

But you probably get the drift by now. Flipping the 1 and the 0 will
determine whether or not you
start with a coloured or a non-coloured row.


If you want to create alternate green bars made up of say 3 rows (or any
other color), you can
also use:

For every three rows:
=MOD(ROW()-1,6)<3

For every four rows:
=MOD(ROW()-1,8)<4

For other number of shadings, just make the 2nd number (6 or 8 in example)
twice as much as the
3rd number (3 or 4 in example)

--
Regards
Ken....................... Microsoft MVP - Excel
Sys Spec - Win XP Pro / XL 97/00/02/03

--------------------------------------------------------------------------
--
It's easier to beg forgiveness than ask permission :-)
--------------------------------------------------------------------------
--

"Bob Phillips" wrote in message
...
Take a look at http://www.xldynamic.com/source/xld.CF.html#rows

--

HTH

RP
(remove nothere from the email address if mailing direct)


"BAase" wrote in message
...
It would be helpful for reading reports, if Excel could shade every
3rd
line
automatically. It would reduce keying errors.