macro or vb to alternating row color

  • Thread starter Thread starter mgm
  • Start date Start date
M

mgm

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?
 
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
 
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)
 
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
 
Thanks guys.... just what I needed!
Peter M said:
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
Click to expand...
 
Back
Top