learn microsoft excel

Formatting Text and Numbers Using a Macro in Excel
Excel 2007
 Excel 2003
 Home
 Macros Excel 2003
 Excel 2010
About Us
 Free Ebook
 Offline Training
 Blog
 Interactive Training

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 dtails.
Excel 2007 Excel 2003 Macros Excel 2007 Macros Excel 2003 Excel 2010
About Us Free Ebook Offline Training Interactive Training