Create a macro that is executed when a cel value chages (not by the user)

Question

Ok I have a worksheet "Goal 0" that with some ranges, make some calculations like...

(in A1)

=SUM(G2:G68)*I17

Then if I add/modify any of the values in 62-G68, the cell is auto calculated (numbers that are mainly negative and some possitive).

The objetive is: According to the sum of the range, find the value of I17 where the result of A1 is equal or more than 0. (Starting from 0, incrementing it 1 by 1, decimals not needed)

Manually I can add change i17 untill it reaches the goal. How ever I want to make it automatically, so if a value in the range of G2 to G68 changes it recalculate the value of I17, untill (A1 calculation gets value equal or higher than 0) but if already is higger or equal than 0 then do nothing.

Hope I explain it well

EDIT: Now I created this code ...

Function IncreaseTheValue()
    If Range("A1") < 0 Then
        Range("I17").Value = 0

        Do While Range("A1").Value < 0
            Range("I17").Value = Range("I17").Value + 1
        Loop
    End If
End Function

And works perfect, how ever it does not fires when I make a chage. How do I do that...

I try adding this in A2 cell but did not worked ...

=IF(A1 < 0, IncreaseTheValue(), "")

Regards

Answer 1

You shouldn't really be doing this as a Function; it is inadequate as you notice, but also not appropriate use of a Function where a Sub or event handler is more appropriate.

Based on your requirements, put the code in the Worksheet_Change event handler. You will need to fine-tune it so that it only fires when a change is made in the range G2:G68.

Try this (untested):

Private Sub Worksheet_Change(ByVal Target as Range)
Application.EnableEvents = False
If Not Intersect(Target, Range("G2:G68")) Is Nothing Then
    If Range("A1") < 0 Then
        Range("I17").Value = 0
        Do While Range("A1").Value < 0
            Range("I17").Value = Range("I17").Value + 1
        Loop
    End If
End If
Application.EnableEvents = True
End Sub

Updated per pnuts comments. This method below will trigger the macro any time any cell changes -- this might be overkill, or it might be necessary if G2:G68 is formulas which change based on changes to other cells. Either method can be fine-tuned to better suit your exact needs.

Private Sub Worksheet_Change(ByVal Target as Range)
Application.EnableEvents = False
    If Range("A1") < 0 Then
        Range("I17").Value = 0
        Do While Range("A1").Value < 0
            Range("I17").Value = Range("I17").Value + 1
        Loop
    End If
Application.EnableEvents = True
End Sub