Results 1 to 9 of 9
  1. #1
    Star Lounger
    Join Date
    Feb 2001
    Location
    South Carolina, USA
    Posts
    66
    Thanks
    0
    Thanked 0 Times in 0 Posts

    borders (version 97)

    I have a spreadsheet with several pages (97 to be exact). I want to have a top, bottom, left and right border on each page without selecting each page and then applying. Is there an easy way to do this in Excel? This would be similar to a page border in Word.

    Thanks,
    Deborah

  2. #2
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: borders (version 97)

    By pages, I will assume that you mean worksheets.

    1- Click on the tab for the first worksheet.

    2- Scroll if necessary and hold down the Shift key and click on the tab for the last sheet. This should select all of the worksheets.

    3- Select the area you want to put borders around.

    4- Select Cells from the Format menu and apply the borders and any other formatting you want.

    5- Click on any sheet tab to unsellect all but that sheet.

    Be extremely careful while you have all of the sheets selected. Anything you do on one sheet will be done on all sheets.
    Legare Coleman

  3. #3
    Star Lounger
    Join Date
    Feb 2001
    Location
    South Carolina, USA
    Posts
    66
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: borders (version 97)

    No. I actually mean pages of data. The one spreadsheet has tons of rows of data and it prints out 97 pages of information. I want a page border around each page of data. I know that I can select the data for that page and apply a border but that is very time consuming. I was wondering if the was a faster way to do this without selecting each individual page. I could view the data in page break preview but still a lot of work.

    Thanks,
    Deborah

  4. #4
    5 Star Lounger
    Join Date
    Mar 2001
    Posts
    989
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: borders (version 97)

    It might depend how 'regular' the pages are. Do they occupy the same number of cells? Could you attach a copy of the spreadsheet, or something similar? (I was thinking you might give the pages range names to make them easier to select). One thought initially - if you select a range and apply a border, you can then select the next area and press the F4 function key to repeat applying the same border. The format painter would also help...

  5. #5
    Platinum Lounger
    Join Date
    Nov 2001
    Location
    Vienna, Wien, Austria
    Posts
    5,009
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: borders (version 97)

    The solution you have received would also only work if the Print Area for each sheet was exactly the same.

    IF all of your sheets have a standard number of cells, then it should be possible to set the borders on the basis of the cell rows - allowing for for repeating rows and collumns if necessary. Unfortunately, although it is possible to set borders on an entire Print Area and to identify the number of Page Breaks by code, it does not seem possible to assign borders to each Page Break.

    Can you post a (suitably censored/abridged) version of the workbook. Alternatively, can you tell us how many cells per page and, if applicable, how many rows/columns repeat per page?
    Gre

  6. #6
    3 Star Lounger
    Join Date
    Dec 2000
    Location
    NJ, USA
    Posts
    239
    Thanks
    2
    Thanked 1 Time in 1 Post

    Re: borders (version 97)

    If the page breaks occur at predictable rows, you could use conditional formatting (for the top & bottom borders - the left and right borders can presumably just be formatted normally). For example, if the page breaks occur at rows 60, 120 , 180, etc., your condtion would read: =MOD(ROW(A1),60)=0. Let me know if this is workable and you need more of an explanation. The formula would need adjusting if the rows at the top repeat on each page.

  7. #7
    Star Lounger
    Join Date
    Feb 2001
    Location
    South Carolina, USA
    Posts
    66
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: borders (version 97)

    I have used this function and it works - Worksheets("Sheet1").Range("a1:M45").BorderAround _
    ColorIndex:=3, Weight:=xlThick.

    However, is there a common to do this at every page break that I could put in the range?

    I really appreciate everyones replys.

    Deborah

  8. #8
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: borders (version 97)

    If the page breaks are at predictable places, then you could write a macro to insert the borders. However, you should only have to insert them one time in that case, and doing that manually would probably take less time than writing the macro.
    Legare Coleman

  9. #9
    Silver Lounger
    Join Date
    Jan 2001
    Location
    West Long Branch, New Jersey, USA
    Posts
    1,921
    Thanks
    6
    Thanked 9 Times in 7 Posts

    Re: borders (version 97)

    Deborah,

    Forgot to mention in my first response: clearly, Hans' method does not require any set number of rows and cols in each Print area. Excel does the work of finding where the breaks are!

    Fred

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •