Formatting Borders

---

As you would expect, the Range object's Interior property deals with the inside of a cell. To affect the outside of a cell, you use the Borders property. Yes, the property Borders is plural. The reason the property name is plural is because each cell actually has six (yes, six) borders. When you change a border, you need to use one of the XlBordersIndex constants to identify which element of the border you want to change. Those elements are xlDiagonalDown, xlDiagonalUp, xlEdgeBottom, xlEdgeLeft, xlEdgeRight, and xlEdgeTop.

Note 

The xlDiagonalDown and xlDiagonalUp constants aren't what you would normally think of as borders; instead, they either draw a line from the top left corner to the bottom right corner of the cell (xlDiagonalDown) or from the bottom left corner to the top right corner of the cell (xlDiagonalUp).

When you reference the Borders property in your VBA code, Excel creates a Borders object. The Borders object's properties are listed in Table 10-8.

Table 10-8: The Borders Object's Properties

Property	Description
Color	Either sets or returns the color of a border using the RGB function
ColorIndex	Either sets or returns the color of a border using a reference to a position in the Excel color palette or either of the constants xlColorIndexAutomatic (sets the color to the default color) or xlColorIndexNone (formats the border with no color)
LineStyle	Sets the style of a line using one of the xlLineStyle constants: xlContinuous, xlDash, xlDashDot, xlDashDotDot, xlDot, xlDouble, xlLineStyleNone, or xlSlantDashDot
Weight	Sets the weight of the line using one of the xlBorderWeight constants: xlHairline, xlMedium, xlThick, or xlThin

You indicate which border you want to set by putting the appropriate xlBordersIndex constant in parentheses after the Borders property is called. For example, the following procedure puts a thin blue border on the bottom edge of cells in the range A1:D1:

Sub BlueBorder()
    With Worksheets("Sheet1").Range("A1:D1").Borders(xlEdgeBottom)
        .LineStyle = xlContinuous
        .Weight = xlThin
        .Color = vbBlue
    End With
End Sub 

Using the Borders property for formatting the borders of your cells is fairly straightforward, but if you want to add a single border around a range of cells, you can use the Range object's BorderAround method to draw an outline around the range. The BorderAround method has the following syntax:

BorderAround(LineStyle, Weight, ColorIndex, Color)

The arguments LineStyle, Weight, ColorIndex, and Color all serve the purposes listed in Table 10-8, but you need to call them as parameters. For example, if you wanted to draw a thin, dashed, red border around the range B1:E1, you would do so using the following procedure:

Sub RedBorder()
Range("B1:E1").BorderAround LineStyle := xlDash, Weight := xlThin, _ 
   Color := vbRed
End Sub 

In this chapter, you've learned how Excel manages colors, how to display the colors in the Excel color palette, and how to change those colors to meet your formatting needs. You've also been introduced to available formatting properties that let you make your headings stand out and your data easier to read and even highlight worksheets that have been changed since the last time they were saved.

---