Editing your macro

After you record a macro, you can change it.

For example, suppose you want the text to be in red. You can re-record the macro, but this change is simple, so it is more efficient to edit the code.

Press Alt+F11 to activate the VB Editor window. Then activate Module1 and insert: Selection.Font.Color = vbRed.

Read more about Formatting Cells Font with VBA.

The macro VBA code should now look something like this:

Sub Helloworld()
'
' Helloworld Macro
' This is my first macro
'
' Keyboard Shortcut: Ctrl+j
'
    Selection.FormulaR1C1 = "Hello World"
    Selection.Font.Bold = True
    Selection.Font.Italic = True
    Selection.Font.Underline = xlUnderlineStyleSingle
    Selection.Font.Color = vbRed
End Sub

When recording macros, you will often manipulate the same object multiple times.

Unfortunately, this code is not as efficient as it should be, because it forces Excel to select and then reference each changed object.

You can save time and improve performance by using the With statement to perform multiple operations on a given object while only referencing the object once. The With statement in this example tells Excel to change four properties, but only references the Font object once, using fewer resources.

Getting into the habit of chunking operations into With statements not only makes macros run faster, but also helps make the code easier to read. Read more about Macro code optimization.

The optimized macro code is as follows:

Sub Helloworld()
'
' Helloworld Macro
' This is my first macro
'
' Keyboard Shortcut: Ctrl+j
'
    With Selection
        .FormulaR1C1 = "Hello World"
        With .Font
            .Bold = True
            .Italic = True
            .Underline = xlUnderlineStyleSingle
            .Color = vbRed
        End With
    End With
End Sub