Perform action when cell value changes

How can you instruct a macro to run when a certain cell changes?

For example, as soon as text in cell A1 changes, a macro is triggered.

Don't know if this is clear?

 

Question Info


Last updated February 27, 2018 Views 130 Applies to:
Answer

Hi,

You are right but I prefer the use of Worksheet_SelectionChange and store the value and test it later in Worksheet_Change to check if it has been changed or not. A lot faster then calling the application object.

Ronse thx for your suggestion, I didn't know about the performance about which one is faster. Let me also post your suggestion below

Dim oldValue As Variant

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    If Not Intersect(Target, Range("A1:B10")) Is Nothing Then
        oldValue = Target.Value
    End If
End Sub

Private Sub Worksheet_Change(ByVal Target As Range)
    If Not Intersect(Target, Range("A1:B10")) Is Nothing Then
        If Target.Value <> oldValue Then
            'Do something
        End If
    End If
End Sub

Wyman W
Human Resources

Did this solve your problem?

Sorry this didn't help.

Great! Thanks for marking this as the answer.

How satisfied are you with this reply?

Thanks for your feedback, it helps us improve the site.

How satisfied are you with this response?

Thanks for your feedback.

Answer

Hi,

You are right but I prefer the use of Worksheet_SelectionChange and store the value and test it later in Worksheet_Change to check if it has been changed or not. A lot faster then calling the application object.

Kind regards,

JP Ronse

If this response answers your question then please mark as Answer.

Did this solve your problem?

Sorry this didn't help.

Great! Thanks for marking this as the answer.

How satisfied are you with this reply?

Thanks for your feedback, it helps us improve the site.

How satisfied are you with this response?

Thanks for your feedback.