Run Macro when cell calculates to negative

[DogsbodyBoy]

Hi

I'm hopeful that someone can help me with a VBA question.

I have two ranges of numeric cells in my Excel worksheet, A1:A6 and C1:C6

In cell A10 I have this formula that totals any negative values in these two numeric ranges:
=SUMIF(A1:A6,"<0")+SUMIF(C1:C6,"<0")
This means that if all the numbers in the two ranges are either zero or positive then cell A10 equals zero, but as soon as any number in the two ranges becomes negative then cell A10 changes to that negative value.

My objective is to add VBA code to the worksheet so that whenever cell A10 becomes negative then I want to automatically run a macro named MyMacro.

All help is much appreciated.

 

[Legacy 143009]

Like this?

Private Sub Worksheet_Change(ByVal Target As Range)

  If Range("$A$10").Value < 0 Then
    Call MyMacro
  End If

End Sub

 

[Fluff]

Another option, this will only call the maco if one of the cells in either range is changed

Private Sub Worksheet_Change(ByVal Target As Range)
   If Not Intersect(Target, Range("A10").Precedents) Is Nothing Then
      If Range("A10").Value < 0 Then Call MyMacro
   End If
End Sub

 

[Legacy 143009]

Not Intersect(Target, Range("A10").Precedents) Is Nothing

Hi @Fluff
I don't know this method. What does it do? Does it check if the value ever turned to negative before? Then throws false?

 

[Fluff]

It checks if the cell that was changed is one of the cells that the formula is looking at.

 

[DogsbodyBoy]

Another option, this will only call the maco if one of the cells in either range is changed

Private Sub Worksheet_Change(ByVal Target As Range)
   If Not Intersect(Target, Range("A10").Precedents) Is Nothing Then
      If Range("A10").Value < 0 Then Call MyMacro
   End If
End Sub

Thank you for your solution; it works very well . I decided I want to have a message box popup before running the macro, so I modified the code like this:

Private Sub Worksheet_Change(ByVal Target As Range)
   If Not Intersect(Target, Range("A10").Precedents) Is Nothing Then
      If Range("A10").Value < 0 Then MsgBox "My message goes here"
      If Range("A10").Value < 0 Then Call MyMacro
   End If
End Sub

It works without error, but is there a way to compile it without repeating the If statement?

 

[DogsbodyBoy]

Like this?

Private Sub Worksheet_Change(ByVal Target As Range)

  If Range("$A$10").Value < 0 Then
    Call MyMacro
  End If

End Sub

Thank you. I tried your approach, but for some reason only the first line of the macro was read and then Excel crashed. It happened repeatedly.

 

[Fluff]

How about

Private Sub Worksheet_Change(ByVal Target As Range)
   If Not Intersect(Target, Range("A10").Precedents) Is Nothing Then
      If Range("A10").Value < 0 Then
         MsgBox "My message goes here"
         Call MyMacro
      End If
   End If
End Sub

 

[DogsbodyBoy]

How about

Private Sub Worksheet_Change(ByVal Target As Range)
   If Not Intersect(Target, Range("A10").Precedents) Is Nothing Then
      If Range("A10").Value < 0 Then
         MsgBox "My message goes here"
         Call MyMacro
      End If
   End If
End Sub

Thanks

 

[Fluff]

Glad we could help & thanks for the feedback.