Run Macro when cell calculates to negative

DogsbodyBoy

New Member
Joined
Oct 24, 2022
Messages
20
Office Version
  1. 365
Platform
  1. Windows
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.
 

Excel Facts

Shade all formula cells
To shade all formula cells: Home, Find & Select, Formulas to select all formulas. Then apply a light fill color.
Like this?
VBA Code:
Private Sub Worksheet_Change(ByVal Target As Range)

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

End Sub
 
Upvote 0
Another option, this will only call the maco if one of the cells in either range is changed
VBA Code:
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
 
Upvote 0
VBA Code:
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?
 
Upvote 0
It checks if the cell that was changed is one of the cells that the formula is looking at.
 
Upvote 0
Another option, this will only call the maco if one of the cells in either range is changed
VBA Code:
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:

VBA Code:
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?
 
Upvote 0
Like this?
VBA Code:
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.
 
Upvote 0
How about
VBA Code:
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
 
Upvote 0
How about
VBA Code:
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
(y) Thanks
 
Upvote 0
Glad we could help & thanks for the feedback.
 
Upvote 0

Forum statistics

Threads
1,223,909
Messages
6,175,312
Members
452,634
Latest member
cpostell

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top