Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations Mike Lewis on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

How can I shade alternate rows automatically?

Best of Excel

How can I shade alternate rows automatically?

by  KenWright  Posted    (Edited  )
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)
Register to rate this FAQ  : BAD 1 2 3 4 5 6 7 8 9 10 GOOD
Please Note: 1 is Bad, 10 is Good :-)

Part and Inventory Search

Back
Top