Formatting Fonts

 < Day Day Up > 



When you program Excel using VBA, it can be easy to forget that the most important part of your worksheet is the data. The easier your data is to understand, the more effective your presentation will be. Figure 10-2 on page 234 offers a somewhat extreme example of the difference between a well-formatted worksheet and a worksheet with no distinction between headings and data.

Most Excel users learn how to use the controls on the Formatting toolbar and in the Format Cells dialog box very early in their Excel careers. In a similar vein, you should make the Font object part of your basic VBA repertoire. Table 10-6 lists and describes the properties of the Font object.

Table 10-6: The Font Object's Properties

Property

Description

Background

The Background property, which is only used for text on charts, can take one of three Excel constants: xlBackgroundAutomatic (which defaults to the chart element's setting), xlBackgroundOpaque (which makes the text box's background appear over other chart elements), or xlBackgroundTransparent (which lets other chart element colors show through).

Bold

Set to True or False, this property determines whether the font will be displayed in bold type.

Color

Uses the RGB property to return or set the font's color.

ColorIndex

Returns or sets a font color to a color in the Excel color palette.

FontStyle

Contains a string that is used to return or set the font style (for example, 'Bold Italic' or 'Regular'). The available styles differ depending on the font.

Italic

Set to True or False, this property determines whether the text will be displayed in italics.

Name

Contains a string that is used to return or set the name of the font in which the text is displayed.

Size

Returns or sets the size of the font, in points.

Strikethrough

Set to True or False, this property determines whether the text will have a horizontal line through the middle of the text.

Subscript

Set to True or False, this property determines whether the text will be formatted as subscript.

Superscript

Set to True or False, this property determines whether the text will be formatted as superscript.

Underline

Returns or sets the type of underlining for the selected text. The style of underlining is represented by one of these Excel constants: xlUnderlineStyleNone, xlUnderlineStyleSingle, xlUnderlineStyleDouble, xlUnderlineStyleSingleAccounting, or xlUnderlineStyleDoubleAccounting.

One of the fundamental strengths of Excel is the ability to create conditional formats, or formats that reflect the value of the data in a cell. As an example, consider the series of If…Then statements introduced in Chapter 5 to change the color of the active cell's contents based on the cell's value.

Sub AvailableCredit()
With ActiveCell
If .Value = "" Then Exit Sub
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 appearance 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
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.'



 < Day Day Up > 



Microsoft Excel 2003 Programming Inside Out
Microsoft Office Excel 2003 Programming Inside Out (Inside Out (Microsoft))
ISBN: 0735619859
EAN: 2147483647
Year: 2006
Pages: 161

Similar book on Amazon

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net