Page 262 - Microsoft Office Excel 2003 Programming Inside Out

Microsoft Office Excel 2003 Programming Inside Out

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,
Chapter 10
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

236
Part 4: Advanced VBA