Microsoft Office Tutorials and References
In Depth Information
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
Search JabSto ::




Custom Search