Run Macro when cell Value Changes

  • Hi,

    I am new to macros and could do with some help.

    I have a formaula in say cell A1, i would like a macro that will check this cell and when the result of the formula changes run another macro.

    Can you please help?

  • Hi Alan,

    Welcome to the forum :)

    You need to have a look at worksheet events. There is a Calculate event that 'fires' whenever Excel recalculates. However, it would be a little cumbersome trying to keep track of the A1 value. A simpler alternative would be to use the Change event based upon the cell that causes the forumla in A1 to change.

    For example, if the formula in A1 depends upon the value in B1 we could use a routine like this:

    Private Sub Worksheet_Change(ByVal Target As Excel.Range)
        If Target.Address = "$B$1" Then
        End If
    End Sub

    This should be placed in the event code for the sheet concerned (right-click the sheet tab concerned and select 'View Code'). It calls a routine called 'Test' in Module1 whenever B1 is changed.


  • The Cell that will be running the Macro is the result of lots of formulas so there is no single cell that could be used as a trigger.

    Could you please advise how to use the Calculate metod?


  • Hi Alan,

    Try something like this:

    If you know what you are expecting in A1 then you can change the data type from Variant to something more appropriate.


  • Hi,,
    I'd like a little twist on this.. i'd like to have it if the change is greater than say 10%.. (ideally up or down 10%) here's what i've got and it doesn't seem to do what i want.. any suggestions?

  • Hi j,

    Try this instead (I've added comments to explain the various steps - any queries, just post back on this thread)


  • ahh,, i'm stupid! :)..
    I had my code working sorta in the workbook code.. when i got ur's i just pasted it over w/out look that it was the Worksheet_Calculate()

    it's now in the worksheet and workign wonderfully! thanks a lot!

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!