Run Macro when cell Value Changes

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
        Module1.Test
    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.

HTH

Hi Alan,

Try something like this:

Dim AOld As Variant


Private Sub Worksheet_Calculate()
    With Range("A1")
        If .Value <> AOld Then
            Module1.Test
        End If
        AOld = .Value
    End With
End Sub

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

HTH

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?

Private Sub Workbook_SheetCalculate(ByVal Sh As Object)
Dim aold As Variant


With Range("A6")
        If .Value < aold Then
 exit sub
     else
            MsgBox "blah blah"
         End If
    aold = .Value * 1.1
End With


End Sub

Hi j,

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

Dim dOld As Double
'store previous value in the cell concerned


Private Sub Worksheet_Calculate()
    Dim dChange As Double
    
    With Range("A6")
        If .Value <> dOld Then
        'has the value in our cell changed?
            If dOld = 0 Then
            'has an 'old' value been stored yet?
                dOld = .Value
                'update the stored value
                Exit Sub
            End If
            dChange = ((.Value - dOld) / dOld)
            'the amount of the change
            If Abs(dChange) &gt; 0.1 Then
            'use the Abs function to ignore the sign of the number
            'if the change is greater than 10% show our message
                MsgBox "blah blah"
            End If
            dOld = .Value
            'update the stored value
        End If
    End With
 
End Sub

HTH

Hey Richie,, thx for replying..
i put the code in and NOTHING happens now.. LOL..
here I put the file online for u so u can see... i am running xl2003 if it even matters... let me know what u find.. thanks man!

http://home.ripway.com/2004-1/61537/Book2.xls

Hi j,

Where did you put the code?

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!