VBA for Excel 2007 tutorial-VBA code to format numbers
VBA code to format numbers To format cells, in VBA you can use the NumberFormat property of the Range object. The NumberFormat property has the following syntax:
You can specify the format string in the same way as of Format Cells dialog.
In the VBA code example below, positive numbers in the range of cells from A1 to A100 will be in blue color and negative in red color. The number of decimal places of the numbers in the range is 2 and the thousand separator(,) is also specified.
To run this example VBA code, copy and paste the VBA code in the block of Worksheet_Activate() procedure. It works when the Excel sheet is active.
VBA code for this example:
Private Sub Worksheet_Activate()
Dim x As Variant
Set x = Range("A1:A100")
x.NumberFormat = "[blue]#,###.00;[red](#,###.00)"