Excel

Trigger a macro when a formula result changes

Ease of Use

Intermediate

Version tested with

2003 

Submitted by:

mdmackillop

Description:

Runs a macro when a value changes as the result of a change elsewhere on the sheet or in the workbook. 

Discussion:

You may occasionally wish a macro to run when a cell value changes, other than the ActiveCell in which data has just been entered/deleted. This code stores the data from the critical cell and runs the specified macro when the value changes. With minor modification, the code will react to changes in cells on worksheets other than the active one (see sample for this code) 

Code:

instructions for use

			

Option Explicit 'Create variable to hold values Dim Monitored Private Sub Worksheet_Activate() Monitored = Range("E8").Value 'Read in value prior to any changes End Sub Private Sub Worksheet_Change(ByVal Target As Range) 'Check target to determine if macro is triggered If Intersect(Target, Union(Range("E6"), Range("E7"))) Is Nothing Then Exit Sub 'Prevent looping of code due to worksheet changes Application.EnableEvents = False 'Compare monitored cell with initial value If Range("E8").Value <> Monitored Then 'Do things as a result of a change DoThings 'Reset Variable with new monitored value Monitored = Range("E8").Value End If 'Reset events Application.EnableEvents = True End Sub Private Sub DoThings() With Range("E9") .Formula = Range("E6") + Range("E7") If .Interior.ColorIndex = 6 Then .Interior.ColorIndex = 8 Else: .Interior.ColorIndex = 6 End If End With End Sub

How to use:

  1. Copy above code.
  2. In Excel press Alt + F11 to enter the VBE.
  3. Press Ctrl + R to show the Project Explorer.
  4. Double-click desired sheet on left
  5. Paste code into the right pane.
  6. Press Alt + Q to close the VBE.
  7. Save workbook before any other changes.
 

Test the code:

  1. Change the values in the indicated cells.
  2. If the Result cell changes value then the designated cell(s) will change to reflect a new value or format as required.
 

Sample File:

ChangeByResult.zip 12.93KB 

Approved by mdmackillop


This entry has been viewed 348 times.

Please read our Legal Information and Privacy Policy
Copyright @2004 - 2014 VBA Express