Trigger Macro only when Cell Value is Increased-CodePudding

its me....again. I am currently trying to have a Macro trigger whenever a specific cell increases on a specific sheet.

After many attempts I have been able to get the Macro to trigger when the cell is changed (increasing or decreasing) but I cannot figure out a way to have this Macro trigger only when the specified cell increases in value.

I have tried to use simple Worksheet_Change with an If Then statement that calls the Macro when the cell value is changed. Again I can't get this to trigger only when the cell increases. Not sure it is possible or if I am even thinking about this is in the right way.

Private Sub Worksheet_Change(ByVal Target As Range)

    If Target.Address  "Range" Then
    Call MyMacro
End If
End Sub

Thank you for any help in advance. This would be really neat and save alot of manual clicking around.

CodePudding user response：

This will hopefully get you on the right track. As per your question it assumes this is required for a single cell only (and in this example code, that cell is B2). The trick is to store the new value, 'undo' the change, grab the old value, reverse the 'undo' by replacing the new value. Then you can test if the values are numbers and, if so, test if the new number is greater than the old number.

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim newValue As Variant, oldValue As Variant
    If Target Is Nothing Then Exit Sub
    If Target.Cells.Count <> 1 Then Exit Sub
    If Target.Column <> 2 Or Target.Row <> 2 Then Exit Sub ' example is using cell B2
    Application.EnableEvents = False
    newValue = Target.Value2
    Application.Undo
    oldValue = Target.Value2
    Target.Value2 = newValue
    Application.EnableEvents = True
    If IsNumeric(oldValue) And IsNumeric(newValue) Then
        If CDbl(newValue) > CDbl(oldValue) Then
            Call MyMacro
        End If
    End If
End Sub

CodePudding user response：

Here is some logic I can think of, you need to have a helper cell to store previous data and compare if it increased. In this sample my helper cell is B1 and the cell I want to track is A1

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim KCells      As Range
    Set KCells = Sheet10.Range("A1")' The variable KeyCells contains the cells that will
    
    If Not Application.Intersect(KCells, Range(Target.Address)) Is Nothing Then  'cause an alert when they are changed.
        If Sheet10.Range("B1").Value < Sheet10.Range("A1").Value Then
            MsgBox "Replace this to call macro"
            Sheet10.Range("B1").Value = Sheet10.Range("A1").Value 'this is  to save the current data incase you change it later.
        End If
    End If
End Sub