Color and Size

[Tom Brown]

I have searched for previous postings on these subjects, but no luck.

I have a workbook with 5 worksheets. They are tabbed along the bottom. 4
of them are assorted columns linked to the master worksheet. I would like
to have each worksheet assign a contrasting color to alternating rows each
time I click on the tab. I frequently add new rows to the first page and
then resort the other worksheets. But, if I have manually colored the rows,
eventually they are a mess with many rows of the same color bunched
together. The intent is to make it easier to read and follow the data on
one line across the sheet.

Is there any practical way to automate this function? I have columns from A
to K and rows up to 600.

This leads me to the other question about limiting the size of the
worksheet. I know it should probably be posted under a separate thread but
it is sort of related to this color question. When I send this workbook via
email, it is over 4 meg in size. If I could get rid of the rows after 600
and the columns past K, it should be smaller in size. I don't know how many
rows are possible but I scrolled to 11,000 and then tried to Delete them.
How many more are there?

Thanks,

Tom

 

[Ed Ferrero]

Hi Tom,

Use conditional formatting. Set the "Formula is" format to =MOD(ROW(A1),2)=1
and the format to a pattern fill.

Ed Ferrero
www.edferrero.com

 

[Tom Brown]

Ed,

Thanks for the help. I think I can use that formula. I have not had a chance
to work with it other than to give it quick try. I assume there is a way to
automatically make that happen on each page.

Did you (or anyone?) have any comment on the question about how to eliminate
the vast amount of unused cells so that the file size is something more
reasonable for emailing?

Thanks,

Tom

 

[Gord Dibben]

See in-line responses.

Ed,

Thanks for the help. I think I can use that formula. I have not had a chance
to work with it other than to give it quick try. I assume there is a way to
automatically make that happen on each page.

If by "page" you mean each worksheet, you will have to do each sheet
individually.

Did you (or anyone?) have any comment on the question about how to eliminate
the vast amount of unused cells so that the file size is something more
reasonable for emailing?

Excel worksheets have a fixed number of rows and columns. These cannot be
deleted. If not used, file size will not be any larger.

If Excel thinks the used range on a worksheet is much larger than you think it
should be see Debra Dalgleish's site for methods of taming the used range on a
worksheet.

http://www.contextures.on.ca/xlfaqApp.html#Unused


Gord Dibben MS Excel MVP