Formatting Data in Excel Using VBA

Formatting Text and Numbers Using a Macro in Excel
You can format text and numbers using a macro in Excel as shown below. The name of the macro has not been changed because the it was modified using a few more lines of code from the last video. The first line makes the range A3:D3 bold. The sixth line makes the cell D4 bold and finally the same cell is colored. ColorIndex 3 gives us red and ColorIndex 5 makes the text blue.

The complete process is implemented as follows:

  • We write the labels from A3:D3 in the worksheet
  • Select the Developer tab
  • From the Code group we select Visual Basic
  • In the menu bar of the Microsoft Visual Basic Window that now pops up, we click on insert and then Module
  • In the workspace we type the code below
  • Click on run

Note: Please remember that once the macro is run the changes to the data cannot be undone. You need to make changes to the code and re-run it or format the data manually to make any changes.

Sub calculate()
Range(“A3:D3”).Font.Bold = True
Range(“A4”) = “Tom”
Range(“B4”) = 5000
Range(“C4”) = Range(“B4”) * 0.5
Range(“D4”) = Range(“C4”) + Range(“B4”)
Range(“D4”).Font.Bold = True
Range(“D4”).NumberFormat = “$#,##0.00”
Range(“D4”).Font.ColorIndex = 5
End Sub

Watch the video below for details.

2 thoughts on “Formatting Data in Excel Using VBA

  1. how to restrict a user form textbox to accept alphanumeric key in a set pattern….. like XXXX9999X in excel VBA coding

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.