Recording Formatting Macros

A Formatting Macro is a macro that contains recorded or written instructions for applying formatting to Excel cells, ranges, rows, columns, or worksheets.

It is useful when the same formatting needs to be applied repeatedly.

Formatting a worksheet manually can take time when many similar reports have to be prepared.

By recording the formatting steps once, you can run the macro later to repeat those steps automatically.

Create a simple table for practice. For example:

We will use this table to record formatting operations.

Click the Developer tab on the Excel Ribbon.

The Developer Tab contains the Record Macro command required for this lesson.

Click Record Macro from the Developer Tab.

Enter a meaningful macro name such as:

FormatStudentTable

Click OK to begin recording.

While recording is active, select the heading row of the table.

For example, select:

A1:C1

The selection itself may become part of the recorded VBA instructions.

With the heading selected, click the Bold button.

Excel records this formatting operation while the Macro Recorder is active.

You can change the font size of the selected heading.

For example, set the heading font size to:

14

This action can also become part of the recorded macro.

You can change the font color of the heading while recording.

The selected font color becomes part of the formatting operations recorded by Excel.

You can also apply a fill color to the heading cells.

For example, select a suitable fill color from the Fill Color option.

Excel records this formatting action as part of the macro.

Borders can make a table easier to read.

While recording, select the required table range and apply All Borders.

The border operation can then be repeated by the macro.

You can change the alignment of the heading or table data.

For example, select the heading and apply Center Alignment.

This action can also be recorded.

Number formatting can also be recorded.

For example, a marks column can be formatted as a number with zero decimal places.

This is useful when preparing standardized reports.

You can adjust the width of columns while recording.

For example, select the Name column and use AutoFit Column Width.

The operation can become part of the formatting macro.

Row height can also be changed during macro recording.

For example, you can adjust the heading row height to make the report easier to read.

One formatting macro can contain multiple formatting operations.

For example:

1. Make the heading bold.
2. Increase the font size.
3. Apply a fill color.
4. Center the heading.
5. Apply borders.
6. Adjust column widths.

All these actions can be recorded in one macro.

After completing the required formatting, go to the Developer Tab.

Click Stop Recording.

The formatting macro is now created.

To test the formatting macro, open the Developer Tab and click Macros.

Select the macro you created, such as:

FormatStudentTable

Click Run after selecting the formatting macro.

Excel executes the recorded formatting instructions.

The worksheet should receive the same formatting operations that were recorded.

You can view the VBA code generated by the Macro Recorder.

Open the Macro dialog, select the macro, and click Edit.

The Visual Basic Editor will open and display the recorded code.

A simple recorded formatting macro may look similar to:

Sub FormatStudentTable()

    Range("A1:C1").Select
    Selection.Font.Bold = True
    Selection.HorizontalAlignment = xlCenter

End Sub

The exact code generated by Excel depends on the formatting actions you perform.

You can record formatting for an entire table instead of only the heading.

For example, you can select:

A1:C4

and apply borders, alignment, number formatting, and other required formatting.

Once a formatting macro has been created, it can be run again whenever the same recorded formatting operation is required.

This can be useful for reports that follow the same layout.

Suppose a company prepares a similar report every month.

A formatting macro can be used to repeat common formatting steps, such as:

• Bold headings
• Apply borders
• Adjust column widths
• Align text
• Format numbers

Formatting macros can also be useful for student reports.

For example, a macro can apply consistent formatting to student marksheets and result reports.

This helps reduce repeated formatting work.

A recorded formatting macro performs the actions that were recorded. It does not automatically understand every possible variation in your data.

For advanced formatting conditions, VBA code may need to be edited or written manually.

• Plan the formatting before recording.
• Use a meaningful macro name.
• Perform only required formatting actions.
• Stop recording when the task is complete.
• Test the macro with sample data.
• Save the workbook as a Macro-enabled workbook.

1. Prepare the Excel table.
2. Open the Developer Tab.
3. Click Record Macro.
4. Enter a macro name.
5. Click OK.
6. Select the required cells.
7. Apply formatting.
8. Stop recording.
9. Open the Macros dialog.
10. Run the formatting macro.

Create a student marks table and record a macro named:

FormatStudentTable

During recording, perform these actions:

1. Make the heading bold.
2. Increase the heading font size.
3. Center the heading.
4. Apply borders to the table.
5. Adjust column widths.

Stop recording and run the macro again to test it.

Formatting Macros are useful when the same Excel formatting operations need to be performed repeatedly. The Macro Recorder can record actions such as bold text, font size, alignment, borders, number formats, and column widths.

After recording, the macro can be run again to repeat the formatting operations. The generated VBA code can also be viewed and edited in the Visual Basic Editor.

Learning formatting macros is an important step toward creating more advanced Excel automation solutions.