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.
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
Watch the video below for details.