VBA Express : Excel - Trigger a macro when a formula result changes

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:

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

Test the code:

Change the values in the indicated cells.
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 362 times.