Page 172 - Excel Progamming Weekend Crash Course

k540629 ch11.qxd 9/2/03 9:34 AM Page 147

Session 11 — Working with Columns, Rows, and Cells 147

containing text as boldface and all cells containing numbers in blue text, making it easy for
the user to tell them apart.

Listing 11-2 Using the SpecialCells method to apply selective formatting

Public Sub FormatRegion()

Dim r As Range

If TypeName(Selection) <> “Range” Then
MsgBox “You must select a range of cells.”
Exit Sub
End If
Set r = Selection
r.SpecialCells(xlCellTypeConstants, xlNumbers).Font.Color = RGB(0, 0, 255)
r.SpecialCells(xlCellTypeConstants, xlTextValues).Font.Bold = True

End Sub

Using the RGB function to specify colors is covered in Session 14.

Cross-Ref

Manipulating Columns and Rows
When you need to work with entire columns or rows, you use the Columns and Rows proper-
ties. These properties return a Range object that references one or more entire columns or
rows. There are three ways to access this property:
The Application.Columns (or Rows) property refers to columns (or rows) in the
active worksheet.
The Worksheet.Columns (or Rows) property refers to columns (or rows) in the spec-
ified worksheet.
The Range.Columns (or Rows) property refers to columns (or rows) in the specified
range.

Because the Application object is always available as an implicit reference
in VBA programs written in Excel, referring to the Columns or Rows property
Tip alone is the same as referring to the Application.Columns or
Application.Rows property.
Be aware that the Range.Columns property does not return a reference to full columns,
but only that part of the columns that fall within the range. The same is true for rows and
the Range.Rows property.