Someone posed this question in a recent Power Excel seminar - he had to format cells with a strange format, and had to do this repeatedly. Learn how to record a simple macro to automate this task. Episode 384 shows you how.
This blog is the video podcast companion to the book, Learn Excel from MrExcel. Download a new two minute video every workday to learn one of the 277 tips from the book!
This blog is the video podcast companion to the book, Learn Excel from MrExcel. Download a new two minute video every workday to learn one of the 277 tips from the book!
Transcript of the video:
Welcome back to the MrExcel netcast, I'm Bill Jelen.
I was doing a power Excel seminar, and someone had a really strange problem.
He said “All day I have to select a cell and format, it was a really weird format, I have to add a light blue background, dark blue bold font, and a blue box around the cell.” And, you know, it's like three or four steps, it was driving me crazy, because he has to do this over and over and over.
This is a great example of where creating a simple little macro will really ease the process.
To create a macro, we want to go to Tools, Macro, and say Record New Macro.
We can give us a name like FormatBlue, and if we want to assign it to a shortcut key, we can put the shortcut key here.
Now a lot of the shortcut keys on the keyboard are already chosen, but Ctrl J and K happen to be open.
I don't know how you would remember that Ctrl+J is the blue shortcut, but let's go ahead and do that.
And then finally, because we want this macro to be available in all of the workbooks we open, we're going to store the macro in the Personal Macro Workbook, click OK.
And now, without selecting any cells, we want to go ahead and format the current selection.
So in his case, you needed a light blue background, blue font, bold, and then you'd go to Format Cells, and on the Border tab choose a blue background, thick outline, click OK.
And now basically, that's all the formatting I had to do.
So, you would hit the Stop Recording button here on the Stop Recording toolbar.
Now basically, we have a shortcut key that's assigned to Ctrl+J, select any cell and hit the keystroke combination, and Excel will format all of those steps for you.
Macro recording is a great way to automate some process like this one, where you have to do 2 or 3 or 4 steps to a cell over and over and over.
Hey, thanks for stopping by, we'll see you next time for another netcast from MrExcel!
I was doing a power Excel seminar, and someone had a really strange problem.
He said “All day I have to select a cell and format, it was a really weird format, I have to add a light blue background, dark blue bold font, and a blue box around the cell.” And, you know, it's like three or four steps, it was driving me crazy, because he has to do this over and over and over.
This is a great example of where creating a simple little macro will really ease the process.
To create a macro, we want to go to Tools, Macro, and say Record New Macro.
We can give us a name like FormatBlue, and if we want to assign it to a shortcut key, we can put the shortcut key here.
Now a lot of the shortcut keys on the keyboard are already chosen, but Ctrl J and K happen to be open.
I don't know how you would remember that Ctrl+J is the blue shortcut, but let's go ahead and do that.
And then finally, because we want this macro to be available in all of the workbooks we open, we're going to store the macro in the Personal Macro Workbook, click OK.
And now, without selecting any cells, we want to go ahead and format the current selection.
So in his case, you needed a light blue background, blue font, bold, and then you'd go to Format Cells, and on the Border tab choose a blue background, thick outline, click OK.
And now basically, that's all the formatting I had to do.
So, you would hit the Stop Recording button here on the Stop Recording toolbar.
Now basically, we have a shortcut key that's assigned to Ctrl+J, select any cell and hit the keystroke combination, and Excel will format all of those steps for you.
Macro recording is a great way to automate some process like this one, where you have to do 2 or 3 or 4 steps to a cell over and over and over.
Hey, thanks for stopping by, we'll see you next time for another netcast from MrExcel!
