Excel VBA Macros: Borders and Formatting

Borders seem very complicated when you record a macro for borders, do any kind of text of text formatting, etc., and look at it in Visual Basic. There are a few ways to simplify this.

Border Formatting Example

You record a macro, make a selection, and set borders. You immediately get something like this:

    Selection.Borders(xlDiagonalDown).LineStyle = xlNone
    Selection.Borders(xlDiagonalUp).LineStyle = xlNone
    With Selection.Borders(xlEdgeLeft)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlMedium
    End With
    With Selection.Borders(xlEdgeTop)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlMedium
    End With
    ...

Need a TL;DR? Essentially it is

1. Removing any diagonal borders through the cell, and
2. replacing each border with a medium size black line.

The details are:

• .Borders(Direction) indicate which border you want changed.
  • xlDiagonalDown affects the line from the top-left to bottom-right of each cell selected.
  • xlDiagonalUp affects the line from the bottom-left to top-right of each cell selected.
  • xlEdgeLeft, xlEdgeRight, xlEdgeUp, and xlEdgeDown affects the left, right, top, and bottom borders of your selection. That means it treats your selection as a box, and the sides are what the code affects, see the above image for details.
  • xlInsideVertical and xlInsideHorizontal affects all the other vertical and horizontal lines (respectively) in your selection that the above didn't cover.
  • If you don't specify a direction, then all
• The parameters you can set for borders are:
  • .LineStyle controls the line style. See the top for details.
  • .Weight controls how thick the line will be.
  • .Color controls the color of the line. You can use RGB format.
  • .TintAndShade controls tint and shade, but they can be ignored if you're using RGB values.

You don't have to stick to just the current selection, it can be modified so that you can change the borders for any cell without having to select it (and there are some good uses for this), so you could substitute Selection with stuff like:

    With Worksheets("Sheet1").Cells(6, 1)              ' This way you can use variables when accessing. 
       .Borders(xlEdgeBottom).LineStyle = xlContinous
    End With

- OR -

    With [D2].Borders(xlEdgeTop)
       .LineStyle = xlContinous
       .Color = RGB (255,0,0)
    End With

Text Formatting

The same can be done with Selection.Font (the parameters above work on this as well). I am listing only important values.

• .Color controls the color of the test (use RGB)
• .PatternColor controls the color of the background (use RGB)
• .HorizontalAlignment controls text horizontal alignment (equal to xlLeft, xlRight, or xlCenter)
• .HorizontalAlignment controls text horizontal alignment (equal to xlTop, xlCenter, or xlBottom)
• .WrapText = (true to wrap)
• .ShrinkToFit = (true to apply)
• .Orientation = (what angle to rotate it by)
• .AddIndent = (true to indent)
• .IndentLevel to determine how far to indent (whole numbers only)
• .MergeCells to merge or remove the merge on the affected cell(s). To not do anything with the cells structure, don't include it.
• .Name = (font name, enclosed in quotes)
• .Size = (font size)
• .Strikethrough = (True to make it strikethrough)
• .Superscript = (True to make it superscript)
• .Subscript = (True to make it subscript)
• .OutlineFont = (True to put an outline on text, but it apparently does nothing)
• .Shadow = (True to put a shadow on text, but it apparently does nothing)