Gridlines and Borders

Display of gridlines:  (#display)

Display of gridlines: File, Page Setup, Sheet
     Gridlines (checked)

Supress gridlines with VBA   (#suppress)

Activewindow.displaygridlines=false

Toggle gridlines with VBA   (#toggle)

 Sub Gridlines() 
 ActiveWindow.DisplayGridlines = Not ActiveWindow.DisplayGridlines
 End Sub

You can create put your macro on a toolbar button .  [2003-08-29],  later in the same thread
Dave Peterson identified builtin button:  Tools|customize|commands Tab|Forms category (near the bottom),
so you don't need the macro after all.

Formatting of interior or background color: (#pattern)

Formatting of interior (background color) will suppress affected gridlines.:
     Select all cells, format, cells, patterns, Cell shading: no color
        (white interior color will wipe out adjacent gridlines, but not borders)
    When using Interior color in Conditional Formatting consider also including borders.

Formatting of borders:   (#format)

Formatting of borders will override gridlines:

• Select all cells (ctrl+A), or a range of cells,
• Format, Cells, Borders
• automatice=black, if ou choose white you won't see them but they will override gridlines.
• Then coose the inside and outside borders.

Something thinner than gridlines.  You can't chose something thinner, but you can choose a dotted border, start as above.

• choose dotted border, and the color, then
• Choose inside and outside borders last, then click on okay.

Setting gridlines for all sheets (#allgridlines)

To set on by default for new sheets you would need to setup your book.xlt and sheet.xlt templates.

For existing sheets: 

• Select all sheets by rightclick on sheet tab, Select all sheets
• Tools, options, view, gridlines
• click on sheet tab, Ungroup sheets

WARNING:  (severe tire damage) you Must ungroup sheets when finished.  Failure to do so means that any change you make such as to cell content will be done for all grouped sheets and will destroy preexisting data.  Any change made to one cell will be done to all sheets in the grouping.

Setting borders for all cells (#allborders)

If you want to put borders on all cells in your current (activesheet) you would select all cells (ctrl+A, except in Excel 2003) then format, cells, borders, choose color, choose each border.  For most border formatting the borders toolbar button will work just fine.

Draw Borders was added in Excel 2002, it does what you would expect it to.  If you do not have the borders toolbar button on your toolbar, you can add it from tools, customize, commands, format.

A flash demonstration of formatting cells is one of many presentations by Mike Alexander at his DataPig Technologies.com - Computer Training and Consulting page or go direct to the Formatting Tricks (flash presentation)     [possible trouble viewing].  These videos remain free but site is becoming a membership site.

If you want to do the same for all sheets in the workbook then right click on the worksheet tab, select all sheets.  With the sheets now grouped any change to the visible sheet will update all of the group worksheets with the same keystrokes.  then as before: 
select all cells (Ctrl+A) then format, cells, borders, choose color, choose each border.

Must ungroup the sheets as any change to the visible worksheet will also be done to the other sheets in the group, and would destroy the content of your workbook very quickly.
  right click on worksheet tab, UNGROUP sheets

If you want to make this so that all future workbooks have borders to start with you would create a  book.xlt  in your XLSTART directory and if you want to add any more sheets to any existing or future workbooks you would create a sheet.xlt worksheet as your default. 
See Templates, book.xlt, sheet.xlt, and beyond

Accessibility Option   (#accessible)

Don't think accessibility options will affect gridlines, but included here anyway.  Accessibility Options would affect all Microsoft Office applicaitons, not just one worksheet.

MSKB 320531 -- OFF: Changes to Fill Color and Fill Pattern Are Not Displayed, 
Incorrect/missing colors may occur with use of High Contrast under Display tab of Accessibility Options.  Colors don't show on monitor.

When you try to change the fill color or the fill pattern, the changes that you selected may not be visible; however, you may be able to see the changes in print preview and on a printed page.

Control Panel, Accessibility Options. Display tab, clear the Use High Contrast check box, OK.

Related   (#related)

• Color Palette -- the 56 Excel Colors
• Font and Border, the border part is more related to HTML and CSS than to Excel.
• MSKB 163230 - Thick Borders May Not Be Printed in Low Printer Resolution (163230) -- When you print thick borders in Microsoft Excel, the borders may be partially printed, or the borders may not be printed at all.
• Sorting cells with borders the borders stay in place and do not move.

Please send your comments concerning this web page to: David McRitchie send email comments

Copyright © 1997 - 2004,  F. David McRitchie,  All Rights Reserved