Automatically Run Macro when Cell Changes

Sometimes it is necessary to have a macro run when the contents of a specific cell changes.  The change in the cell could be typing something in or overwriting previous contents. To do this you will need to use some Excel VBA.

cell-changeWhat you need to do is go into the VBA environment of Excel. Normally we would create a new module to enter our code on to. But for this to work we need to have the code within the worksheet that the cell change will be occurring. So in my example it will be Sheet 1. Also, for this example, I will be referencing cell A1 to be the cell that has the changes.

Within the VBE for Excel double click Sheet1 under Microsoft Excel Objects on the left hand side. This will open a window on the right hand side. In that window enter the following code:

Private Sub Worksheet_Change(ByVal Target As Range)

If Target.Address = "$A$1" Then

Application.EnableEvents = False

'ENTER YOUR MACRO HERE THAT WILL RUN WHEN CELL CHANGES

Application.EnableEvents = True

End If

End Sub

For your version of this the parts to change are listed below.  Be sure to enter the macro within the correct worksheet otherwise this will not work.

If Target.Address = "$A$1" Then is referencing cell A1. Change this whatever cell you want.

Replace where it says 'ENTER YOUR MACRO HERE THAT WILL RUN WHEN CELL CHANGES with the code that you want to run when the cell changes.

Now go back to the worksheet. Whenever you make a change to cell A1, your macro should automatically run.

8 thoughts on “Automatically Run Macro when Cell Changes

  1. Sarang

    if A1 contains a formula , and the value changes in that formula , how can we activate a macro with that ??

  2. Matt

    Hi,

    How do you make the macro work if the cell that changed is chagning due to a formula that references other cells in another workesheet? Is it possible to active the amcro from a cell with a formula that causes a change? I tried using a functino but it didn;t quite work.

    Thanks,

  3. Imran

    Hi there,
    I am using the following to hide/unhide a worksheet. I want to the Taxable worksheet to become visible when the value in cell B8 on my input sheet is Taxable, otherwise, it should be hidden. The macro does not automatically runs and when it runs, it gives me the following error:

    Compile Error: Expected Sub End

    Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Address = “$B$8” Then
    Application.EnableEvents = False
    Sub ConditionalHide()
    Sheets(“Input”).Activate
    If Range(“B8”).Value = “Nontaxable” Then
    Sheets(“Taxable”).Visible = False
    Else: Sheets(“Taxable”).Visible = True
    Application.EnableEvents = True
    End If
    End Sub

  4. victorjesse

    Does this really work with Excel 2010? Below is the code I entered in VBA as instructed. As a test, my macro asks for a simple action to move the cursor to another cell as selected – when cell A1 is changed. This does nothing. Please see code below. Please tell me where I am going wrong before I try more complex code.

    Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    If Target.Address = “$A$1” Then
    Application.EnableEvents = False
    Range(“m48”).Select
    Application.EnableEvents = True
    End If
    End Sub

  5. Excel Geek

    Yes it does work with 2010. I have just double checked it. The code needs to be installed on the worksheet module that will have cell reference, for example Sheet1. You also need to change the speech marks in your code as they are not correct format for Excel. Paste the code into your module and then delete and retype the speech marks.

    Thanks.

  6. Alice

    This is great, it is possible to amend the code so that a different macro runs based on what the cell value is changed to?

    e.g. cell value = 1 – run macro 1
    cell value = 2 – run macro 2

    etc.

    Thanks

  7. Pingback: Macros/VBA - assistance required

  8. Alex

    Managed to make it work one but stopped working and never again thereafter 🙁
    Not even by copying and pasting the above lines and adapting the calculation to cell A1

    Thanks for the help in any case.

Leave a Reply

Your email address will not be published. Required fields are marked *