row color change

20 views
Skip to first unread message

mpaziz

unread,
Aug 18, 2003, 1:47:44 PM8/18/03
to
how can I change color on even rows in the worksheet and
set as a defoult for new sheet

Rob

unread,
Aug 18, 2003, 2:33:29 PM8/18/03
to
Simplest way:
Open a new blank Workbook.
Highlight the second row by clicking on the row number.
Color the row.
Select the first 2 rows by click-dragging the first 2 row
numbers.
Copy.
Seclect the whole sheet [edit, select all]
Paste.
Now the whole workbook is colored, on even rows.
Save it.
Open this sheet as a template when you want the colored
rows. Consider making it read only so you don't
accidentally overwrite it.

>-----Original Message-----
>how can I change color on even rows in the worksheet and
>set as a defoult for new sheet

>.
>

Ken Wright

unread,
Aug 18, 2003, 2:52:04 PM8/18/03
to
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 / XL2K & XLXP

----------------------------------------------------------------------------
Attitude - A little thing that makes a BIG difference
----------------------------------------------------------------------------

"mpaziz" <azi...@hotmail.com> wrote in message news:023f01c365b0$d433f450$a301...@phx.gbl...

Reply all
Reply to author
Forward
0 new messages