borders (version 97)

Topic

New Reply

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

Reply | Quote

Replies

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.

Reply | Quote

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

Reply | Quote

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…

Reply | Quote

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?

Reply | Quote

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.

Reply | Quote

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

Reply | Quote

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

Reply | Quote

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.

Reply | Quote