Page 208 - Excel Progamming Weekend Crash Course

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

Session 14 — Formatting a Worksheet 183

‘ accommodate the labels.
.Range(“B1”).EntireColumn.AutoFit
‘ Put the loan payment formula in C7
.Range(“C7”).Value = “=-PMT(C5/12, C6*12, C4)”
‘***
‘ Format cells C4 and C7 as currency.
.Range(“C4”).NumberFormat = “$#,###.00”
.Range(“C7”).NumberFormat = “$#.##”
‘ Format cell C5 as percent.
.Range(“C5”).NumberFormat = “0.00%”
‘ Set background to gray for all cells.
.Range(“A1:D9”).Interior.Color = RGB(200, 200, 200)
‘ Set background to white for input cells.
.Range(“C4:C6”).Interior.Color = vbWhite
‘***
End With
‘ Save the workbook.
wb.SaveAs Filename:=”LoanCalculator”
End Sub

The worksheet created by this new version of the program is shown in Figure 14-6. I
think you’ll agree that the worksheet is much more attractive than the original version. In
addition, having the data entry cells differentiated from the other worksheet cells makes
the user’s job much easier. There are still more improvements that can be made, and those
are covered in Session 24.

Figure 14-6 The Loan Calculator worksheet created by the program in Listing 14-2