excel - How to run macro if based on other cells which automatically changes by formula -

as per subject, need run macro based on other cells.

here case :

1. cells g3 until end of row contains data used formula =if(b3="";"";(sumif('incoming goods'!$f$3:$f$1048576;'current stock'!b3;'incoming goods'!$m$3:$m$1048576)-(sumif('outgoing goods'!$d$4:$d$1048576;'current stock'!b3;'outgoing goods'!$j$4:$j$1048576))))--> need convert formula vba

2. cells h3 should contain : if g3.value = 0 "out of stock", else " "

and sheet must calculate every time data in g3 change automatically or additional data on sheet.

already tried code :

private sub worksheet_calculate()     dim current worksheet     dim rng1 range     dim target range      set current = worksheets("current stock")     set rng1 = current.range("g:g")     set target = range("h:h")      each rng1 in target         if rng1.value2 = "0"             target.value2 = "out of stock"         else             exit sub         end if     next  end sub 

however, above code not working. try using private sub selection change() , private sub selection change() still not working.

any suggestion?

thanks in advance

the answer first part below:

activecell.formular1c1 = _         "=if(r[2]c[1]="""","""",(sumif('incoming goods'!r3c6:r1048576c6,'current stock'!r[2]c[1],'incoming goods'!r3c13:r1048576c13)-(sumif('outgoing goods'!r4c4:r1048576c4,'current stock'!r[2]c[1],'outgoing goods'!r4c10:r1048576c10))))" 

handy tip: convert excel formula code, hit record macro button, click on cell, press f2 key, press enter, , stop recording macro. code in own module in vba editor.