How to Use Excel VBA to Run Macro When Cell Value Changes

In this article, you will learn how to run VBA macro when cell value changes in Excel. We will show you 3 examples with simple explanations of each code to make sure you acquire this skill with ease.

To automate Excel functionality, learning how to run VBA macro when cell value changes can be a very advanced skill. This can reduce manual intervention, dynamic data processing and save time. From data validation to generating alerts and seamless data updates, this knowledge will offer endless possibilities for efficiency gains.

How to Use Excel VBA to Run Macro When Cell Value Changes: 3 Easy Examples

We will show you 3 examples on how to work with VBA macro when a specific cell value changes or a cell in a specific range changes. We will also show you install a drop-down list to the cell and work with VBA macro when you change the value in the drop-down menu.

If you are new to Microsoft Visual Basic Application, follow this article on How to Write VBA Code in Excel. We will try to give simple explanations for each code so that you can use this skill from now on. So, let’s get to the main part of the article.

1. Run Macro When a Specific Cell Value Changes

In this dataset, we have some students and their marks in certain exams. We also created a dynamic table to find the top n number of students. If we want to know how we did that, follow this link on How to Create a Dynamic Top 10 List in Excel.

Now, we want to show a message in cell E18 when we change value in cell F4. So, we need to create an Event that will get triggered every time we change value in cell F4. So, our code should be in the Worksheet_Change event.

To create an event, you don’t need to create a Module. Follow these steps to better understand.

Right-click on your worksheet name and select View Code.

In the code window, you will see two drop-down menus at the top: one for the Object and one for the Event.
Select the Worksheet object from the left drop-down menu, and Change event from right drop-down menu. This will create a subroutine.

Inside the subroutine write the following code. So, the whole code will look like below.

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 (meaning we changed cell F4), the code assigns a message to cell E18. We designed this code to update the message in cell E18 whenever changed cell F4, it provides a dynamic message based on the value entered in F4.

Now, if you change value in cell F4 you will see a dynamic message on cell E18 like in the video.

2. Run Macro When a Cell Value in a Range is Changed

In this section, we will create a code that will give us a message when we change value in any cell of a range. Like in this dataset, we want to get a message if we change anyone’s mark and the message should be whose mark we changed.

Right-click on your worksheet name and select View Code.

Create a Worksheet_Change subroutine following the previous method. Then write 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 is used to check if the Target range (i.e., the range that was changed) intersects with the range C5:C20 in the worksheet. In other words, it checks if the change occurred within the specified range.

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

This part retrieves the value of the cell in column B corresponding to the row of the changed cell. Then create a message that is concatenated with the retrieved value to show in the message box.

Now if you change any value in column C, the message box will show the student’s name like in the video.

Read More: How to Edit Macros in Excel

3. Run Macro When Value in Drop-Down List Changes

This time we will work with drop-down list. We will work with the first example and create a drop-down box in cell F4 to choose the number of students who topped.

Select cell F4.
Under the Data ribbon, go to Data Tools group and select Data Validation command from the Data Validation drop-down.

In the Allow box, choose List from the options.
On the Source box, type numbers with commas (,) in between them.
Press OK and you’re done.

As you can see, this added the drop-down list to cell F4.

Now, right-click on your worksheet name and select View Code.
Create a Worksheet_Change subroutine following the previous method. Then write 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

Now, if you change the value in cell F4 using the drop-down list, you will see a dynamic message on cell E18 like in the video.

How to Run VBA Macro When Cell Value Changes By Formula In Excel

In this dataset, we have a formula in column F based on the SUM function to calculate the total mark for each student. Now, we want to create a VBA macro so that if the output of these formulas somehow gets changed, a message will notify us.

Since in this case, we want a calculation change to trigger the the code, we will write our code inside Worksheet_Calculate event.

Next, right-click on your worksheet name and select View Code.
Select the Worksheet object from the left drop-down menu, and Calculate the event from the right drop-down menu. This will create a subroutine.

Inside the subroutine write the following code. So, the whole code will look like below.

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

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

Dim flag As Boolean
flag = False

We declared a Boolean variable flag. We set the value to be False. It indicates that initially there is no change.

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

In this code, it is looping 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, we promptly update the oldval array with the present value. Next, we fetch the corresponding student’s name from column B and exhibit a message box, revealing the name of the student whose marks have been updated. The flag variable serves as a tracker to signal the presence of a change, and as soon as the it detects the first updated mark, the loop terminates to present a singular message box.

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

After the loop through rows 6 to 10, the code 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 the range F6:F10 during the current recalculation. In this case, the code updates the oldval array with the current values of cells F6 to F10, effectively storing the latest formula results.

Now, if you change the value of the SUM formula, the code will activate the message by stating the student’s name like in the video.

How to Run VBA Macro When Cell is Selected

Suppose, you want to display your selected cell’s content or value on the Status Bar. This means every time you select a cell, that cell’s content would show up on the Status Bar. So, we must create an Event to trigger it every time we select a cell. So, our code should be in the Worksheet_SelectionChange event.

First, right-click on your worksheet name and select View Code.
In the code window, you will see two drop-down menus at the top: one for the Object and one for the Event.
Select the Worksheet object from the left drop-down menu, and it will automatically select SelectionChange event and create a subroutine.

Inside the subroutine, write code to display selected cell content. So, the whole code will look like below.

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

This line of code sets the message for Status Bar. It collects the value from selected cell and retrieves the Student’s name and subject and concatenates the message.

Now if you select any cell, you will get the content of that cell on the Status Bar just like in the video.

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, Excel offers alternative methods to respond to cell value changes without using VBA. 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. These features provide automated responses and do not require writing VBA code.

2. Can I use VBA to trigger actions in other worksheets or workbooks based on cell value changes in a specific worksheet?
Ans: Yes, with VBA, you can use the Worksheet_Change event to detect cell value changes in a specific worksheet. Then, you can write code to trigger actions in other worksheets or workbooks based on those 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, when using VBA to respond to cell value changes, 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 with the article.

Run Macro When Cell Value Changes.xlsm

Conclusion

Mastering VBA macros to respond to cell value changes empowers Excel users to unleash the full potential of automation. We hope by learning to run Excel VBA macro when cell value changes can help our reader become more efficient in their professional life.

Get FREE Advanced Excel Exercises with Solutions!

2 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

How to Use Excel VBA to Run Macro When Cell Value Changes

How to Use Excel VBA to Run Macro When Cell Value Changes: 3 Easy Examples

1. Run Macro When a Specific Cell Value Changes

2. Run Macro When a Cell Value in a Range is Changed

3. Run Macro When Value in Drop-Down List Changes

How to Run VBA Macro When Cell Value Changes By Formula In Excel

How to Run VBA Macro When Cell is Selected

Frequently Asked Questions

Conclusion

Related Articles

Leave a reply Cancel reply