macro or vb to alternating row color
 

I would like to have my spreadsheets to have rows that have a color/grey
alternate to make reading the data clearer and easier to follow. Is there a
way to write a macro or vba that could be triggered and accessed by all my
workbooks?

macro or vb to alternating row color
 

MGM

The easiest way of achieving this is to use the conditional formating
facility. Firstly select all the cells that you want the formating to apply
too. Then on the worksheet menu bar select Format, Conditional Format.

In the left hand dropdown box choose 'Formula Is' rather than the default
'Cell Value Is'. In the edit box that then appears enter:

=IF(MOD(ROW(),2)=1,TRUE,FALSE)

Then click on the 'Format' button and set the Pattern style to grey (or
whatever other colour you fancy).

If you want a wider spacing between highlighted lines simply change the
number in the formula above, for example:

=IF(MOD(ROW(),5)=1,TRUE,FALSE) highlights every 5th row.

This is much easier than using VB.

Hope it works for you!

Peter Morris
"mgm" wrote in message
...
I would like to have my spreadsheets to have rows that have a color/grey
alternate to make reading the data clearer and easier to follow. Is there
a
way to write a macro or vba that could be triggered and accessed by all my
workbooks?

macro or vb to alternating row color
 

You don't need all of the formula that Peter provides

=MOD(ROW(),2)=1

will return either TRUE or FALSE that CF will respond to. In general, the
formula is

=MOD(ROW(),m)=n

where m = number of rows in the group, and n is the first striped row. n
mustn't equal or exceed m.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"Peter M" wrote in message
...
MGM

The easiest way of achieving this is to use the conditional formating
facility. Firstly select all the cells that you want the formating to
apply
too. Then on the worksheet menu bar select Format, Conditional Format.

In the left hand dropdown box choose 'Formula Is' rather than the default
'Cell Value Is'. In the edit box that then appears enter:

=IF(MOD(ROW(),2)=1,TRUE,FALSE)

Then click on the 'Format' button and set the Pattern style to grey (or
whatever other colour you fancy).

If you want a wider spacing between highlighted lines simply change the
number in the formula above, for example:

=IF(MOD(ROW(),5)=1,TRUE,FALSE) highlights every 5th row.

This is much easier than using VB.

Hope it works for you!

Peter Morris
"mgm" wrote in message
...
I would like to have my spreadsheets to have rows that have a color/grey
alternate to make reading the data clearer and easier to follow. Is
there
a
way to write a macro or vba that could be triggered and accessed by all
my
workbooks?

macro or vb to alternating row color
 

Hi Peter
this can be shortened to
=MOD(ROW(),2)=1

see also
http://www.cpearson.com/excel/banding.htm
for more general formulas
Frank


Peter M wrote:
MGM

The easiest way of achieving this is to use the conditional formating
facility. Firstly select all the cells that you want the formating
to apply too. Then on the worksheet menu bar select Format,
Conditional Format.

In the left hand dropdown box choose 'Formula Is' rather than the
default 'Cell Value Is'. In the edit box that then appears enter:

=IF(MOD(ROW(),2)=1,TRUE,FALSE)

Then click on the 'Format' button and set the Pattern style to grey
(or whatever other colour you fancy).

If you want a wider spacing between highlighted lines simply change
the number in the formula above, for example:

=IF(MOD(ROW(),5)=1,TRUE,FALSE) highlights every 5th row.

This is much easier than using VB.

Hope it works for you!

Peter Morris
"mgm" wrote in message
...
I would like to have my spreadsheets to have rows that have a
color/grey alternate to make reading the data clearer and easier to
follow. Is there a way to write a macro or vba that could be
triggered and accessed by all my workbooks?

macro or vb to alternating row color
 

Bob, Frank,

Many thanks - I'll delete my 'the easiest way' and replace with 'nearly the
easiest way'!

I didn't realise that that would work - clearly a philosophy to employ
elsewhere.

Thanks.

Peter

"Frank Kabel" wrote in message
...
Hi Peter
this can be shortened to
=MOD(ROW(),2)=1

see also
http://www.cpearson.com/excel/banding.htm
for more general formulas
Frank


Peter M wrote:
MGM

The easiest way of achieving this is to use the conditional formating
facility. Firstly select all the cells that you want the formating
to apply too. Then on the worksheet menu bar select Format,
Conditional Format.

In the left hand dropdown box choose 'Formula Is' rather than the
default 'Cell Value Is'. In the edit box that then appears enter:

=IF(MOD(ROW(),2)=1,TRUE,FALSE)

Then click on the 'Format' button and set the Pattern style to grey
(or whatever other colour you fancy).

If you want a wider spacing between highlighted lines simply change
the number in the formula above, for example:

=IF(MOD(ROW(),5)=1,TRUE,FALSE) highlights every 5th row.

This is much easier than using VB.

Hope it works for you!

Peter Morris
"mgm" wrote in message
...
I would like to have my spreadsheets to have rows that have a
color/grey alternate to make reading the data clearer and easier to
follow. Is there a way to write a macro or vba that could be
triggered and accessed by all my workbooks?

macro or vb to alternating row color
 

Thanks guys.... just what I needed!
"Peter M" wrote in message
...
Bob, Frank,

Many thanks - I'll delete my 'the easiest way' and replace with 'nearly
the
easiest way'!

I didn't realise that that would work - clearly a philosophy to employ
elsewhere.

Thanks.

Peter

"Frank Kabel" wrote in message
...
Hi Peter
this can be shortened to
=MOD(ROW(),2)=1

see also
http://www.cpearson.com/excel/banding.htm
for more general formulas
Frank


Peter M wrote:
MGM

The easiest way of achieving this is to use the conditional formating
facility. Firstly select all the cells that you want the formating
to apply too. Then on the worksheet menu bar select Format,
Conditional Format.

In the left hand dropdown box choose 'Formula Is' rather than the
default 'Cell Value Is'. In the edit box that then appears enter:

=IF(MOD(ROW(),2)=1,TRUE,FALSE)

Then click on the 'Format' button and set the Pattern style to grey
(or whatever other colour you fancy).

If you want a wider spacing between highlighted lines simply change
the number in the formula above, for example:

=IF(MOD(ROW(),5)=1,TRUE,FALSE) highlights every 5th row.

This is much easier than using VB.

Hope it works for you!

Peter Morris
"mgm" wrote in message
...
I would like to have my spreadsheets to have rows that have a
color/grey alternate to make reading the data clearer and easier to
follow. Is there a way to write a macro or vba that could be
triggered and accessed by all my workbooks?