Page 114 - Microsoft Office Excel 2003 Programming Inside Out

Microsoft Office Excel 2003 Programming Inside Out

You can then fill in the details of your procedure using the Visual Basic Editor to pick objects,
built-in functions, properties, events, and so on using the Object Browser. The following
code listing contains a procedure that checks the contents of the active cell and, when the
value matches any of the tests in the If…Then statement, changes the cell’s font color to the
named color.

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

The colors listed in the preceding code are represented by VBA constants, but there are many millions of
specific colors available to you. For more information on using colors to format the contents of items in your
workbook, see Chapter 10, “Formatting, Excel Objects.”

It’s interesting to notice that the seemingly equivalent procedure that follows, which uses a
Select Case statement to test the values in the active cell, actually generates an incorrect result.
Sub AvailableCreditCase()
Remaining = ActiveCell.Value
Select Case Remaining
Case ""
Exit Sub
Case Is >= 10000
ActiveCell.Font.Color = vbGreen
Case Is <= 9999
ActiveCell.Font.Color = vbBlue
Case Is <= 4999
ActiveCell.Font.Color = vbBlack
Case Is <= 1000
ActiveCell.Font.Color = vbRed
End Select
End Sub

Chapter 5

88
Part 2: Visual Basic for Applications