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:

     RangeObject.NumberFormat="Format string"

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 for Excel: format numbers with VBA code

VBA code for this example:

Private Sub Worksheet_Activate()
    Dim x As Variant
    Set x = Range("A1:A100")
    x.NumberFormat = "[blue]#,###.00;[red](#,###.00)"
End Sub


This website intents to provide free and high quality tutorials, examples, exercises and solutions, questions and answers of programming and scripting languages:
C, C++, C#, Java, VB.NET, Python, VBA,PHP & Mysql, SQL, JSP, ASP.NET,HTML, CSS, JQuery, JavaScript and other applications such as MS Excel, MS Access, and MS Word. However, we don't guarantee all things of the web are accurate. If you find any error, please report it then we will take actions to correct it as soon as possible.