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

ok have worksheet "goal 0" ranges, make calculations like...

(in a1)

=sum(g2:g68)*i17 

then if add/modify of values in 62-g68, cell auto calculated (numbers negative , possitive).

the objetive is: according sum of range, find value of i17 result of a1 equal or more 0. (starting 0, incrementing 1 1, decimals not needed)

manually can add change i17 untill reaches goal. how ever want make automatically, if value in range of g2 g68 changes recalculate value of i17, untill (a1 calculation gets value equal or higher 0) if higger or equal 0 nothing.

hope explain well

edit: created code ...

function increasethevalue()     if range("a1") < 0         range("i17").value = 0          while range("a1").value < 0             range("i17").value = range("i17").value + 1         loop     end if end function 

and works perfect, how ever not fires when make chage. how do that...

i try adding in a2 cell did not worked ...

=if(a1 < 0, increasethevalue(), "") 

regards

you shouldn't doing function; inadequate notice, not appropriate use of function sub or event handler more appropriate.

based on requirements, put code in worksheet_change event handler. need fine-tune fires when change made in range g2:g68.

try (untested):

private sub worksheet_change(byval target range) application.enableevents = false if not intersect(target, range("g2:g68")) nothing     if range("a1") < 0         range("i17").value = 0         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. method below trigger macro time cell changes -- might overkill, or might necessary if g2:g68 formulas change based on changes other cells. either method can fine-tuned better suit exact needs.

private sub worksheet_change(byval target range) application.enableevents = false     if range("a1") < 0         range("i17").value = 0         while range("a1").value < 0             range("i17").value = range("i17").value + 1         loop     end if application.enableevents = true end sub