XL: How to Run a Macro When Certain Cells Change

ID: Q142154

The information in this article applies to:

Microsoft Excel 98 Macintosh Edition
Microsoft Excel 97 for Windows
Microsoft Excel for Windows 95, version 7.0
Microsoft Excel for Windows, versions 5.0, 5.0c
Microsoft Excel for the Macintosh, versions 5.0, 5.0a
Microsoft Excel for Windows NT, version 5.0

SUMMARY

In Microsoft Excel, you can create a macro that is called only when a value is entered into a cell in a particular sheet or in any sheet that is currently open.

Note, however, that you should not call macros unnecessarily because they slow down the performance of Excel.

MORE INFORMATION

Microsoft provides programming examples for illustration only, without warranty either expressed or implied, including, but not limited to, the implied warranties of merchantability and/or fitness for a particular purpose. This article assumes that you are familiar with the programming language being demonstrated and the tools used to create and debug procedures. Microsoft support professionals can help explain the functionality of a particular procedure, but they will not modify these examples to provide added functionality or construct procedures to meet your specific needs. If you have limited programming experience, you may want to contact a Microsoft Certified Solution Provider or the Microsoft fee-based consulting line at (800) 936-5200. For more information about Microsoft Certified Solution Providers, please see the following page on the World Wide Web:

http://www.microsoft.com/mcsp/

For more information about the support options available from Microsoft, please see the following page on the World Wide Web:

http://www.microsoft.com/support/supportnet/overview/overview.asp

In many instances, a macro should run only when a certain number of cells have values entered into them (referred to as the "key cells" in this document). In order to prevent a large macro from running every time a value is entered into a cell of a sheet, you must check to see if the ActiveCell is one of the key cells. To accomplish this, use the Intersect method on the ActiveCell and the range containing the key cells to verify the ActiveCell is one of the key cells. If the ActiveCell is in the range containing the key cells, you can call the macro.

To run this sample macro:

Open a new workbook.

On Sheet1 in cell A11, type the following formula:

=SUM(A1:A10)

Copy this formula to cells B11, C11, and D11.

In the example used here, the macro causes the background color of cells A11, B11, and C11 to change to red (or ColorIndex 3) if that cell's value is larger than 50; otherwise, the cell's background color will be normal. This is accomplished by checking for entries in cells A1:A10, B1:B10, and C1:C10. Each time an entry is made in any cell on Sheet1, the DidKeyCellsChange macro runs. This macro determines if the ActiveCell is in the range of key cells (A1:C10). If it is, the KeyCellsChanged macro is called to re-evaluate cells A11:C11.

The following macros work together to perform this functionality. The "Auto_Open" macro must be run once in order for the other macros to be called.

Insert a new module sheet. In Microsoft Excel 97, on the Tools menu, point to Macro, and then click Visual Basic Editor. In the Editor, click Module on the Insert menu. In Microsoft Excel versions 5.0 and 7.0, point to Macro on the Insert menu, and then click Module.

Type the following macro code into the module sheet:


Sub auto_open()

   ' Run the macro DidCellsChange any time a entry is made in a
   ' cell in Sheet1.
   ThisWorkbook.Worksheets("Sheet1").OnEntry = "DidCellsChange"

End Sub


Sub DidCellsChange()
  Dim KeyCells as String
   ' Define which cells should trigger the KeyCellsChanged macro.
   KeyCells = "A1:A10, B1:B10, C1:C10"

   ' If the Activecell is one of the key cells, call the
   ' KeyCellsChanged macro.
   If Not Application.Intersect(ActiveCell, Range(KeyCells)) _
   Is Nothing Then KeyCellsChanged

End Sub

Sub KeyCellsChanged()
   Dim Cell as Object
   ' If the values in A11:C11 are greater than 50...
   For Each Cell In Range("A11:C11")
   If Cell > 50 Then

   ' Make the background color of the cell the 3rd color on the
   ' current palette.
   Cell.Interior.ColorIndex = 3

   Else
   ' Otherwise, set the background to none (default).
   Cell.Interior.ColorIndex = xlNone

   End If
   Next Cell

End Sub

Save the workbook, and then close it.

Open the workbook.

This will cause the Auto_Open macro to run.

In cell A1, type 60.

Cell A11 should change to a Red background color because you changed a cell in the KeyCells range and the value of cell A11 became greater than 50.

In cell D1, type 60

Nothing will happen to the color of D11, even though the value in D11 is now greater than 50. Nothing happens because cell D1 is not in the KeyCells range.

REFERENCES

In Microsoft Excel version 7.0, for more information about running procedures when an event occurs, click the Index tab in Microsoft Excel Help, type the following text

procedures, running

double-click the appropriate text to go to the "Running procedures when an event occurs" topic.

Additional query words: 5.00a 5.00c 8.00 xl97

Keywords : kbprg kbdta kbdtacode PgmHowto KbVBA
Version : MACINTOSH:5.0,5.0a,98; WINDOWS:5.0,5.0c,7.0,97; winnt:5.0
Platform : MACINTOSH WINDOWS winnt
Issue type : kbhowto