Excel – Number Formatting in VBA Macro

Number formatting controls how numbers on cells are displayed, it has no effect on cells that contain text. In Microsoft Excel, if we want to apply number formatting we can use Format Cells dialog box.

To format numbers in VBA macro we can use the NumberFormat property. Following are some number formatting codes to format numbers.

Here is the example VBA code to display numbers with no commas or special characters, and no decimal places:

Sub NumFormat()
 Range("A1").NumberFormat = "0"
End Sub

We can display positive and negative numbers differently. Following number format code will display negative numbers in red color.


To specify a display color, include the color name enclosed in square brackets at the start the format code. The available color names are:

  • Black
  • Blue
  • Cyan
  • Green
  • Magenta
  • Red
  • White
  • Yellow

LINK (Vbatutor.blogspot.com)