How to make a Worksheet Calculate event when a range of cells change?

**Invicta084** · 02-15-2014, 03:59 PM

Hi,

I have the following code that I would like to trigger when cells B26:U26 change to something other then 0 due to a formula? How was I make this happen??

Please Login or Register  to view this content.

**6StringJazzer** · 02-15-2014, 04:22 PM

The only way I know of to detect a change in the result of a formula is to do a Worksheet_Change event on the worksheet containing the data on which the formula is based. If all your data is in one worksheet then this becomes pretty easy.

Second, when you say cells "B26:U26 change to something other then 0" do you mean any of them, or all of them? Here is how you would do this if the formulas in that range use precedents on the same sheet, and you are interested in whether any of them are non-zero. However, note that this will also run if something on the sheet changes and none of the cells in B26:U26 have changed. If you need that amount of control, you need to do analysis for specifically what can cause a change to the values in those cells. As is often the case, if you attach your file I could give a more comprehensive answer.

Please Login or Register  to view this content.

**Invicta084** · 02-15-2014, 04:30 PM

Thanks for the reply, I will attach the sheet for you to look at. Basically in sheet2 there is some calculations that are running from an RTD client that is updating values, anything with RTD or linking to sheet2 will be constantly updating. Ideally I only want Gseek and gseek1 to run when any of the cells in B26:U26 are not 0. Its slightly over my vba skills and comprehension, thanks for the help.

Options Vol.xlsm

**6StringJazzer** · 02-15-2014, 06:20 PM

Try this, although the data is all #N/A so I couldn't test it and I have no idea whatsoever what RTD is.

Edit: Your existing code checked for a Worksheet_Change on Sheet3, but the data on Sheet3 never changes so it will never be called. Everything on Sheet3 is a formula that refers to other formulas in Sheet3, or to data on Sheet2. So you have to detect the Worksheet_Change on Sheet2, and then manage the results in Sheet3. There is now code in the modules for both sheets.

**Invicta084** · 02-15-2014, 07:20 PM

Hey I changed the code to worksheet calculate instead of change, it wasn't doing anything once I ran the client to make the RTD functions return numbers. When I changed it to calculate it worked for a few iterations but then it went haywire on me and the values for the Vol started to be like 800000000000000000000000000000000000000000000%. Not sure why but... I can provide you the information to use the RTD function in a PM if you are interested.