Adding steps to an existing Excel macro

Applies to
Microsoft Excel 2000

Once you've recorded some macros and seen how powerful this capability is, you may want to learn how to change your macros to make them even more useful.

For example, say your corporate standards change and in addition to bold red text with a red border, you want the cell background to be yellow. You could record the macro all over again and reassign it to the button and keystroke, but there's an easier way. You can record another macro that makes the cell background yellow, and copy the additional instructions from this macro to your original macro. Here's how:

  1. Record the new macro: Point to Macro on the Tools menu, and then click Record New Macro. Type a name for the macro and click OK.
  2. Format the cell with a yellow fill: Click Cells on the Format menu, click the Patterns tab, click the yellow box under Color, and then click OK.
  3. Click the Stop Recording button.
  4. Copy the instructions from the new macro: On the Tools menu, point to Macro, and then click Macro. Click the name of your new macro, and then click Edit.

When you edit a macro, Excel opens the Visual Basic® Editor and displays the instructions it recorded when you created the macro. The yellow fill macro looks something like this:

Yellow fill macro code

This window may look complex but it's simpler than it seems. The line Sub YellowFill() is the start of the macro, and End Sub is the end of the macro. The lines starting with apostrophes, such as ' YellowFill Macro, are descriptive comments. The instructions that format the cell are the lines from With through End With.
  1. Select the instructions that format the cell:

Yellow fill instructions selected

  1. On the Edit menu, click Copy.
  2. Now edit your original RedFlagAccounts macro: On the File menu in the Visual Basic Editor, click Close and Return to Microsoft Excel. On the Excel Tools menu, point to Macro, click Macros, click the RedFlagAccounts macro, and then click Edit.
  3. Click the blank line following the comments, and then click Paste on the Edit menu:

Where to paste the instructions

  1. Click Close and Return to Microsoft Excel. Now when you run the RedFlagAccounts macro in Excel, it makes the cell background yellow as well as making the number bold and red and the cell border red.

Make your macros available for other workbooks

By default, macros you record are stored in the current workbook. To make a macro available in other workbooks, you can copy the macro to the workbooks where you want to use it. For more information about copying macros, type copy entire macro in the Office Assistant or on the Answer Wizard tab in the Excel Help window, click Search. Next click Copy a macro, and then click Copy a macro module to another workbook.

More information

For more information about working with macros, type macros in the Office Assistant or on the Answer Wizard tab in the Excel Help window, and then click Search.

For information about getting Visual Basic Help in Excel, type get VB Help in the Office Assistant or on the Answer Wizard tab in the Excel Help window, and then click Search.