We can record a macro in excel easily. To automate the repetitive task in excel, macro recorder can be used. You can record a macro applying the format you want, and then replay the macro whenever needed.
Let’s say we have to automate some formatting task that can be applied on selected cells. Below are the steps to do that-
- Open a new workbook or you can use an existing workbook.
- Select the some range in Excel
- Click on Record Macro button.
Record macro button is available in 3 places in a workbook.
- In the View Tab>>Macros>>Record Macro
- In footer of the workbooks.
- In the Developer Tab>>Record Macro
You can use any of the option to record a macro. click on any record macro option.
Below given window will be displayed. Put the macro name by default it will be Macro 1. You can give the shortcut Key to access this macro. Here I am putting Ctrl+m short key. Select This workbook in Store macro in box and put some description if you want. It is not mandatory. Click on OK button. Macro Recording will be started.
Give some formatting to your selected cells like-border, font name, font size, text alignment etc.
Now stop the macro recording from the same button, from where you start recording.
Now our macro has been recorded successfully. Test this select some range in excel sheet and go to Developer Tab>>Click on Macros
Macro list will be displayed in Macro window. Here we have only one macro.
In the below image we will run this macro in range I4:K9. Click on Run button.
Same formatting will applied on selected range ( I4:K9)
Since we have given a shortcut key Ctrl+m while recording the macro, so we can select some range and press Ctrl+m to apply this formatting.
To see the code in Visual Basic editor press Alt+11.
Now the time to save this workbook. We have to save this workbook as Macro enabled or Excel Binary or Excel 97-2003 format so that macro can be available in the workbook.