1. Computer problem? Tech Support Guy is completely free -- paid for by advertisers and donations. Click here to join today! If you're new to Tech Support Guy, we highly recommend that you visit our Guide for New Members.

Shade alternating lines in Excel

Discussion in 'Business Applications' started by mrap1, Jul 2, 2002.

Thread Status:
Not open for further replies.
Advertisement
  1. mrap1

    mrap1 Thread Starter

    Joined:
    Apr 19, 2002
    Messages:
    74
    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?
     
  2. Rockn

    Rockn

    Joined:
    Jul 29, 2001
    Messages:
    21,189
    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
     
  3. cri

    cri

    Joined:
    Sep 1, 2001
    Messages:
    4
    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
     
  4. mrap1

    mrap1 Thread Starter

    Joined:
    Apr 19, 2002
    Messages:
    74
    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?
     
  5. cri

    cri

    Joined:
    Sep 1, 2001
    Messages:
    4
    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.
     
  6. jbcalg

    jbcalg

    Joined:
    Oct 29, 2001
    Messages:
    2,056
    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
     
  7. mrap1

    mrap1 Thread Starter

    Joined:
    Apr 19, 2002
    Messages:
    74
    Thanks jbcalg, your method was the easiest and best matched my needs.
     
  8. jbcalg

    jbcalg

    Joined:
    Oct 29, 2001
    Messages:
    2,056
    my pleasure ;)

    thanks for posting back to let others know what worked for you
     
  9. Sponsor

As Seen On
As Seen On...

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.

Join over 733,556 other people just like you!

Loading...
Thread Status:
Not open for further replies.

Short URL to this thread: https://techguy.org/85682