vba - Autorun Module in Excel -

vba - Autorun Module in Excel -

i have module in excel need run automatically whenever cell changed in worksheet.

as stands now, runs if go edit mode on cell has function in , nail come in or if nail ctrl+alt+f9. how can accomplish this? don't much vb or excel macros , modules nor did write code.

this code in module...

function colorfunction(rcolor range, rrange range, optional sum boolean) dim rcell range dim lcol long dim vresult lcol = rcolor.interior.colorindex if sum = true each rcell in rrange if rcell.interior.colorindex = lcol vresult = worksheetfunction.sum(rcell, vresult) end if next rcell else each rcell in rrange if rcell.interior.colorindex = lcol vresult = 1 + vresult end if next rcell end if colorfunction = vresult end function

i set application.volatile in origin of macro, seems work expected when test it, note andy g's commenet above:

...changing colour won't cause recalc, nor trigger alter event ...changing cell value has no effect on formula result

if want color alter trigger re-calculation, need different approach.

function colorfunction(rcolor range, rrange range, optional sum boolean) dim rcell range dim lcol long dim vresult application.volatile lcol = rcolor.interior.colorindex if sum = true each rcell in rrange if rcell.interior.colorindex = lcol vresult = worksheetfunction.sum(rcell, vresult) end if next rcell else each rcell in rrange if rcell.interior.colorindex = lcol vresult = 1 + vresult end if next rcell end if colorfunction = vresult end function

excel vba excel-vba