Hi,

I have a worksheet which has over 50,000 rows of data and 10 columns.
I would like to appy background color to all this data to make it easy
to read.

Here is what I need:

a. All the odd numbered rows in the range a5:j50004 should
have a gray background color.

b. All the even numbered rows in the range a5:j50004 should
have a yellow background color.

c. This color scheme should remain when existing rows are
deleted from this range.

d. This color scheme should remain when new rows are
inserted into this range.

Please show me the VBA code to do all of the above.
In what part of the spreadsheet should this code reside?


Thanks.
Luther



------------------------------------------------
~~ Message posted from http://www.ExcelTip.com/
~~ View and post usenet messages directly from http://www.ExcelForum.com/

Lothario, try this if you don't mind the whole row to be colored
Alternating row colors
Select the range you want to format. Format Conditional Formatting. In the
left dropdown, change ""Cell Value is"" to ""Formula Is"". In the formula
box type. =MOD(ROW(),2)=0 Click the Format.. button. Click the Patterns tab
and pick a color " if you want every other row a different color click add
and put =MOD(ROW(),1)=0 in that box.


--
Paul B
Always backup your data before trying something new
Using Excel 2000 & 97
Please post any response to the newsgroups so others can benefit from it
** remove news from my email address to reply by email **
"lothario" wrote in message
...
Hi,

I have a worksheet which has over 50,000 rows of data and 10 columns.
I would like to appy background color to all this data to make it easy
to read.

Here is what I need:

a. All the odd numbered rows in the range a5:j50004 should
have a gray background color.

b. All the even numbered rows in the range a5:j50004 should
have a yellow background color.

c. This color scheme should remain when existing rows are
deleted from this range.

d. This color scheme should remain when new rows are
inserted into this range.

Please show me the VBA code to do all of the above.
In what part of the spreadsheet should this code reside?


Thanks.
Luther



------------------------------------------------
~~ Message posted from http://www.ExcelTip.com/
~~ View and post usenet messages directly from http://www.ExcelForum.com/

VBA code isn't needed.

Select all the columns

Use formatting = Conditional formatting


condition 1

Formula is =mod(row(),2)=0
Pattern is yellow

condition 2
formula is = Mod(row(),2)=1
Pattern is gray

--
Regards,
Tom Ogilvy


lothario wrote in message
...
Hi,

I have a worksheet which has over 50,000 rows of data and 10 columns.
I would like to appy background color to all this data to make it easy
to read.

Here is what I need:

a. All the odd numbered rows in the range a5:j50004 should
have a gray background color.

b. All the even numbered rows in the range a5:j50004 should
have a yellow background color.

c. This color scheme should remain when existing rows are
deleted from this range.

d. This color scheme should remain when new rows are
inserted into this range.

Please show me the VBA code to do all of the above.
In what part of the spreadsheet should this code reside?


Thanks.
Luther



------------------------------------------------
~~ Message posted from http://www.ExcelTip.com/
~~ View and post usenet messages directly from http://www.ExcelForum.com/

On Sat, 1 Nov 2003 22:37:09 -0500, Tom Ogilvy <[email deleted] wrote:

VBA code isn't needed.

Select all the columns

Use formatting = Conditional formatting


condition 1

Formula is =mod(row(),2)=0
Pattern is yellow

condition 2
formula is = Mod(row(),2)=1
Pattern is gray

It should be noted that this would be a hell of a lot faster than using
VBA.
--
auric "underscore" "underscore" "at" hotmail "dot" com
*****
Stupidity is no excuse for not thinking.

Yup, formatting = Conditional formatting worked.
And it is the fastest.
Thanks.



------------------------------------------------
~~ Message posted from http://www.ExcelTip.com/
~~ View and post usenet messages directly from http://www.ExcelForum.com/