Page 204 - Excel Progamming Weekend Crash Course

k540629 ch14.qxd 9/2/03 9:34 AM Page 179

Session 14 — Formatting a Worksheet 179

You can manipulate the borders of a range in two ways:

If you use the Borders collection without an argument, you affect all borders.
If you use the Borders collection with one of the constants from Table 14-3 as an
argument, you affect only the specified border.

For example, this code places double borders on all four edges and the interior borders of
the current selection:
Selection.Borders.LineStyle = xlDouble

In contrast, this code places a double border only at the top edge of the selection:

Selection.Borders(xlEdgeTop).LineStyle = xlDouble
The aspects of a border that you can control are described in Table 14-4.

Table 14-4 Properties of the Border Object

Property Description Settings
Color The color of the border An RGB value (as described earlier in
this session); the default is black

LineStyle The style of the border, such as xlContinuous, xlDash, xlDashDot,
a continuous line, a dashed line, xlDashDotDash, xlDot, xlDouble,
a dotted line, and so on xlSlantDashDot, xlLineStyleNone
Weight The thickness of the border, xlHairline, xlThin, xlMedium,
from hairline to a thick rule xlThick

To remove a border, set its LineStyle property to xlLineStyleNone.

Tip
The program in Listing 14-1 demonstrates using VBA code to add borders to a worksheet.
It also uses some of the font formatting techniques that were covered earlier in this session.
The objective is to format a data table that has a row of column headings and a column of
row headings. The format that is applied is described in comments in the listing. The pro-
gram formats the table (using the CurrentRegion property) that the active cell is in.
Figure 14-5 shows the results of running this program.

Listing 14-1 A program to format a worksheet table

Public Sub FormatTable1()

‘ Formats the table that the active cell is
‘ in as follows:
Continued