Advertisement

There's no such thing as a stupid question, but they're the easiest to answer.
Login
Search

Advertisement

Business Applications Business Applications
Search Search
Search for:
Tech Support Guy > > >

Shade alternating lines in Excel


(!)

mrap1's Avatar
mrap1 mrap1 is offline
Member with 74 posts.
THREAD STARTER
 
Join Date: Apr 2002
02-Jul-2002, 09:19 PM #1
Shade alternating lines in Excel
Can someone tell me how I can shade alternating lines two different colors? I know how to do it manually, but I have this huge spreadsheet (about 900 lines long) and it would take me forever to do it one by one. So is there any quick way to make one line white and the next grey throught the entire sheet?
Rockn's Avatar
Computer Specs
Member with 21,189 posts.
 
Join Date: Jul 2001
Location: Somalia of the North, MN
Experience: Disenfranchised American
02-Jul-2002, 09:33 PM #2
There are more than one way to do it, but the easiest is to select the whole range of cells toy want formatted line that and select format > auto format and select List1
cri's Avatar
cri cri is offline
Junior Member with 4 posts.
 
Join Date: Sep 2001
03-Jul-2002, 06:23 AM #3
Using Edit|Autoformat has the drawback to be static, i.e. should you have to sort/insert rows your rows then you will have to re-apply it AND _all_ other cell/font formatting too...

The most elegant way I know is to use ConditionalFormating:

a) Select your range, taking mental note of the cell address you started with, say C9 (or switch on the FormulaBar)
b) Format|ConditionalFormatting:
Formula is =(EVEN(ROW($C9))=ROW($C9))
Format: Choose a pattern, case printout is to be faxed choose a very light one

Remarks
- The overhead is surprisingly low, ~ 0.5 kB for 1000 x 50 cells
- If all workbook users have ToolPak installed, then the formula can be shortened to =ISEVEN(ROW($C9)
- If you want to detect _row_ sorting errors, write (the analog of) C9 in the formula
mrap1's Avatar
mrap1 mrap1 is offline
Member with 74 posts.
THREAD STARTER
 
Join Date: Apr 2002
03-Jul-2002, 01:22 PM #4
I think the first method is much easier, I couldn't even get the second method to work. The only draw back is that you can't choose what colors you want to use. Any idea of how to change the colors instead of black and white?
cri's Avatar
cri cri is offline
Junior Member with 4 posts.
 
Join Date: Sep 2001
04-Jul-2002, 12:01 AM #5
These Autoformats are predefined. I do not know how to alter them.
Re easier: Not always the quicker way, depends whether you will expand/sort your worksheet afterward.

If you follow the recipy by the letter it will work. Make sure that:
- Select the whole range you want to format, this also when making changes to the ConditionalFormatting.
- Write the formula correctly (in the ConditionalFormating dialog !) and check whether Excel did not transform $C9 into '$C9', which sometimes happens when making a syntax error.
jbcalg's Avatar
Senior Member with 2,056 posts.
 
Join Date: Oct 2001
Location: W. Canada
04-Jul-2002, 09:33 AM #6
you can also use the paintbrush / format painter icon to copy the format

ie
highlight the row (by clicking on the row number), right click, format cells, patterns, choose the colour

so, leave the first row as is (ie it's already white), then change the next row to gray

highlight both rows (click on the row number and drag down one row) , click on the paintbrush

highlight and drag down the number of rows you want

note: this will copy and paste ALL the formating - numbers, left, right justify, etc

the other prob is the whole row gets the colour, not just the columns you're using

you can also drag and highlight across the columns you're using, then do the format painter
mrap1's Avatar
mrap1 mrap1 is offline
Member with 74 posts.
THREAD STARTER
 
Join Date: Apr 2002
06-Jul-2002, 05:06 PM #7
Thanks jbcalg, your method was the easiest and best matched my needs.
jbcalg's Avatar
Senior Member with 2,056 posts.
 
Join Date: Oct 2001
Location: W. Canada
07-Jul-2002, 12:06 AM #8
my pleasure

thanks for posting back to let others know what worked for you
As Seen On

BBC, Reader's Digest, PC Magazine, Today Show, Money Magazine
WELCOME TO TECH SUPPORT GUY!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.


(clock)
THIS THREAD HAS EXPIRED.
Are you having the same problem? We have volunteers ready to answer your question, but first you'll have to join for free. Need help getting started? Check out our Welcome Guide.

Search Tech Support Guy

Find the solution to your
computer problem!




Currently Active Users Viewing This Thread: 1 (0 members and 1 guests)
 
Thread Tools


WELCOME
You Are Using: Server ID
Trusted Website Back to the Top ↑

Content Relevant URLs by vBSEO 3.3.2