Page 259 - Microsoft Office Excel 2003 Programming Inside Out

Formatting Excel Objects

If .Value <= 1000 Then .Font.Color = vbRed
If .Value > 1000 Then .Font.Color = vbBlack
If .Value > 4999 Then .Font.Color = vbBlue
If .Value > 9999 Then .Font.Color = vbGreen
End With
End Sub
If you don’t want to change the color of a cell’s entire contents, you can use the Range object’s
Characters property to format some part of a cell’s value. The Characters property uses the
following syntax to indicate which characters in the cell’s value to change:

Characters (start, length)

The start argument represents the character with which you want to begin your reformatting,
and length indicates the number of characters (including the first) to reformat. Once you’ve
identified the characters, you use the Character object’s Font property to change the appear­
ance of the characters. For example, if you knew that the fourth through eighth characters of
an order tracking string identified the sales agent, you could display those characters in
bold type.

Sub HighlightAgent()
Dim MyCell As Range
Dim strFirst, strLast, strAllCells, strCategory As String

strFirst = InputBox(“Enter the first cell.”)
strLast = InputBox(“Enter the last cell.”)
strAllCells = strFirst & “:” & strLast
For Each MyCell In Range(strAllCells).Cells
Range(MyCell.Address).Select Chapter 10
MyCell.Characters(4, 5).Font.Bold = True
Next MyCell
End Sub

For more information on manipulating text strings and other cell values, and for finding the beginning
and ending of substrings that match a given pattern, see Chapter 9, “Manipulating Data with VBA.”

Formatting Cells

For the purposes of formatting, cells are divided into two sections: the interior and the bor­
der. And, just as you can change the appearance of the gridlines and sheet tabs of a workbook,
you can change the color and fill pattern of the interior of a cell. Changing the fill color of a
cell to yellow or light orange can help set off the values in the formatted cells. In fact, as seen
in Figure 10-2, many of the Excel AutoFormats use colored cell interiors to set a worksheet’s
data labels apart from the data itself.

233
Part 4: Advanced VBA