How to Use Excel VBA to Run a Macro When the Cell Value Changes - 3 Examples

Overview:

Example 1. Running a Macro When a Specific Cell Value Changes

The dataset showcases students and their marks. A dynamic table was created to find the top n number of students.

To display a message in E18 when the value in F4 changes:

Right-click your worksheet name and select View Code.

In the code window, select the Worksheet in the left drop-down menu, and Change in the right drop-down menu. This will create a subroutine.

Enter the following code.

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Address = "$F$4" Then
        Range("E18").Value = "These " & Target.Value & " students will get a scholarship"   
    End If
End Sub

Code Breakdown

If Target.Address = "$F$4" Then
        Range("E18").Value = "These " & Target.Value & " students will get a scholarship"
End If

The Target parameter inside the event handler subroutine represents the changed cell. If the condition is TRUE ( F4 changed), the code displays a message in E18. This code updates the message whenever F4 changes.

Example 2 – Running a Macro When a Cell Value in a Range is Changed

Right-click your worksheet name and select View Code.

Create a Worksheet_Change subroutine following the previous method. Enter this code.

Private Sub Worksheet_Change(ByVal Target As Range)
           If Not Intersect(Target, Range("C5:C20")) Is Nothing Then
                     MsgBox Range("B" & Target.Row).Value & "'s mark has been changed"
           End If
End Sub

Code Breakdown

If Not Intersect(Target, Range("C5:C20")) Is Nothing Then

The Intersect function checks if the Target range (the range that was changed) intersects with C5:C20 in the worksheet.

MsgBox Range("B" & Target.Row).Value & "'s mark has been changed"

This part of the code retrieves the value of the cell in column B corresponding to the row of the changed cell. It creates a message that is concatenated with the retrieved value.

Read More: How to Edit Macros in Excel

Example 3 – Running a Macro When the Value in the Drop-Down List Changes

A drop-down list was created in F4.

Select F4.
In Data, go to Data Tools and select Data Validation in the Data Validation drop-down.

In Allow, choose List.
In Source, enter numbers with commas (,) in between.
Click OK.

The drop-down list was added to F4.

Right-click your worksheet name and select View Code.
Create a Worksheet_Change subroutine following the previous method. Enter this code.

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Address = "$F$4" Then
        Range("E18").Value = "These " & Target.Value & " students will get scholarship"
    End If
End Sub

If you change the value in F4 using the drop-down list, a dynamic message will be displayed in E18.

How to Run a VBA Macro When the Cell Value Changes using a Formula In Excel

Enter the code in the Worksheet_Calculate event.

Right-click your worksheet name and select View Code.
Select Worksheet in the left drop-down menu, and Calculate in the right drop-down menu. This will create a subroutine.

Enter the following code.

Private Sub Worksheet_Calculate()
Static oldval(6 To 10) As Variant
Dim flag As Boolean
flag = False
    For i = 6 To 10
        If Range("F" & i).Value <> oldval(i) Then
            oldval(i) = Range("F" & i).Value
            Dim studentName As String
            studentName = Range("B" & i).Value
            MsgBox studentName & "'s mark is updated"
            flag = True
        Exit For
        End If
    Next i
    
    If flag = False Then
        For i = 6 To 10
            oldval(i) = Range("F" & i).Value
        Next i
    End If
End Sub

Code Breakdown

Static oldval(6 To 10) As Variant

declares a static array named oldval to store the previous values of the cells in column F (row 6 to 10). The Static keyword means that the array will retain its values between subsequent calls to the event handler, ensuring you can track changes over time.

Dim flag As Boolean
flag = False

a Boolean variable flag is declared. The value is set to False.

For i = 6 To 10
        If Range("F" & i).Value <> oldval(i) Then
            oldval(i) = Range("F" & i).Value
            Dim studentName As String
            studentName = Range("B" & i).Value
            MsgBox studentName & "'s mark is updated" 
            flag = True
            Exit For
        End If
Next i

loops through rows 6 to 10, examining whether the current formula in cell F(i) yields a distinct result from the previously stored value in the oldval array. Upon detecting any alteration, it updates the oldval array with the present value. The corresponding student’s name is found in column B and a message box is displayed with the name of the student whose marks have been updated. The flag variable serves as a tracker to signal the presence of a change. As soon as it detects the first updated mark, the loop presents a singular message box.

If flag = False Then
        For i = 6 To 10
            oldval(i) = Range("F" & i).Value
        Next i
End If

evaluates If flag = False then and checks if there is any change during the iteration. If the flag is still False, it means there was no change detected in the formula results within F6:F10. The code updates the oldval array with the current values of F6:F10, storing the latest formula results.

If you change the value of the SUM formula, the code will activate the message, stating the student’s name.

How to Run a VBA Macro When the Cell is Selected

You want to display the content of a selected in the Status Bar.

Right-click your worksheet name and select View Code.
Select Worksheet in the left drop-down menu, and it will automatically select the SelectionChange event and create a subroutine.

Enter this code to display the selected cell content.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    If Not Intersect(Target, Range("C6:F10")) Is Nothing Then
        Application.StatusBar = Range("B" & Target.Row).Value & " got " & Target.Value & " in " & Range(Cells(5, Target.Column).Address).Value
    End If
End Sub

Code Breakdown

If Not Intersect(Target, Range("C6:F10")) Is Nothing Then

The Intersect function checks whether the selected cell (Target) intersects with the range C6:F10.

Application.StatusBar = Range("B" & Target.Row).Value & " got " & Target.Value & " in " & Range(Cells(5, Target.Column).Address).Value

sets the message in the Status Bar. It collects the value from selected cell and retrieves the Student’s name and subject and concatenates the message.

If you select any cell, you will see its content in the Status Bar.

Frequently Asked Questions

1. Are there alternative methods or Excel features that I can use instead of VBA to respond to cell value changes?
Ans: Yes. You can use Excel’s built-in features like Data Validation, Conditional Formatting, or formulas with Worksheet Functions to trigger actions based on cell changes.

2. Can I use VBA to trigger actions in other worksheets or workbooks based on cell value changes in a specific worksheet?
Ans: Yes.You can use a code to trigger actions in other worksheets or workbooks based on changes. VBA allows for automation and coordination between different sheets or workbooks.

3. Are there any limitations or considerations to keep in mind when using VBA to respond to cell value changes?
Ans: Yes. Consider the following:

Performance impact: Frequent changes can slow down Excel.
Error handling: Handle potential errors to avoid crashes.
Event triggers: Avoid recursive events by disabling them during execution.
Compatibility: Ensure compatibility with different Excel versions.
Security: Enable macros only from trusted sources to prevent potential security risks.

Download Practice Workbook

Download this file to practice.

Run Macro When Cell Value Changes.xlsm

Get FREE Advanced Excel Exercises with Solutions!

4 Comments

nunof Dec 25, 2023 at 5:20 PM

Hello,

Thanks for the post, contains useful information.

I suggest that you make explicitly clear that the macro runs every time *any* cell in the concerned sheet changes.

I have been faced with a performance problem when I needed to associate an action with a change in value in just ONE specific cell ($K$10) in my Book1 Sheet1.

Book1 Sheet1 has hundreds of cells with values that need to be updated from time to time :
– any of these updates, having nothing to do with cell $K$10, triggers the execution of the macro
– many cells in have formulas that reference other cells (Excel calls them ‘dependents’) f.i. cell $V$7 has formula ‘= $A$1 + $A$2’. The macro is called when cell $A$1 (or $A$2) is updated and then again when the dependent cell $V$7 is updated
– finally many cells in Sheet1 have links to external workbooks. Every time I open Book1 and choose to update links the macro runs for every update that is made

Granted, the macro runs ‘for nothing’, i.e. the ‘If Intersect’ evaluates to ‘false’ since my only cell of interest, $K$10, is not impacted, but still has a very heavy impact on performance.

Thanks again for the post, I learned a lot by reading through it.

Regards,

nunof
- Reply
  Lutfor Rahman Shimanto Dec 27, 2023 at 6:57 PM
  Hello NUNOF
  
  Thanks for your beautiful words. Your appreciation means a lot to us. We are delighted to know you have learned a lot from our blog.
  
  The issue you describe hampers performance for sure in such scenarios. I have gone through the article and also investigated your problem. What I have found: Perhaps you are calling your desired macro within another worksheet event like Worksheet_Calculate instead of the Worksheet_Change event of cell $K$10, which results in calling the macro unintentionally.
  
  When the cell values change by formulas, the Worksheet_Change event will not trigger. So, call your macro or sub-procedure within a change event. In your case, the code within the sheet module can be like the following:
```
Private Sub Worksheet_Change(ByVal Target As Range)

    If Not Intersect(Target, Range("K10")) Is Nothing Then
        
        Call GreetingToNUNOF
    
    End If

End Sub


Sub GreetingToNUNOF()
    MsgBox "Hello from ExcelDemy!"
End Sub
```
  Hopefully, you have found the idea to overcome your situation. Good luck!
  
  Regards
  Lutfor Rahman Shimanto
  Excel & VBA Developer
  ExcelDemy
Michel Claes Jul 18, 2024 at 8:14 PM

Thank you very much. This is the first article that explian the diferent change detection and how to use them. Only thing that I do not undertsant is the use of “oldval”: how is it initialized (at first run of the sub; why it is not updated when a change is detected instrad of when it is not.
- Reply
  Shamima Sultana Jul 24, 2024 at 11:03 PM
  
  Hello Michel Claes,
  
  You are most welcome. Thank you for your feedback! I’m glad you found the article helpful.
  Static oldval(6 To 10) As Variant: Declares a static array oldval to store previous values of cells F6 to F10, so the values persist between subroutine calls.
  The variable “oldval” is initialized at the first run of the subroutine to store the initial cell value before any changes occur. It’s updated only when a change is not detected to retain the previous value for comparison. This way, it can accurately identify when a change happens in the cell value.
  If you have any further questions or need more clarification, feel free to ask!
  
  Regards
  ExcelDemy

How to Use Excel VBA to Run a Macro When the Cell Value Changes – 3 Examples

Example 1. Running a Macro When a Specific Cell Value Changes

Example 2 – Running a Macro When a Cell Value in a Range is Changed

Example 3 – Running a Macro When the Value in the Drop-Down List Changes

How to Run a VBA Macro When the Cell Value Changes using a Formula In Excel

How to Run a VBA Macro When the Cell is Selected

Frequently Asked Questions

Related Articles

Leave a reply Cancel reply