Trouble activating macro

Trowa - Jul 3, 2009 at 08:48 AM
 Trowa - Jul 6, 2009 at 08:01 AM

I've got a macro which runs on 12 worksheets.
When the result of a formula changes I want the macro to do his job.

What I do now is manually selecting each worksheet and then copy-paste a formula in the same cell.
For the copy-paste action I've made a macro. The macro will copy-paste a formule in the same cell for each of the 12 sheets. But I still need to select the sheet before my initial macro is activated.

I've searched the internet and I found something which may clearify my issue:

How do I run a macro every time a certain cell changes its value?
There is an event called Worksheet_Change which is triggered when a value is entered (it will not fire when a formula result changes).

What's between the brackets is exactly what I want: Fire the macro when a formula result changes.
Does anybody know how to do this?

Best regards,

2 responses

venkat1926 Posts 1863 Registration date Sunday June 14, 2009 Status Contributor Last seen August 7, 2021 811
Jul 3, 2009 at 08:00 PM
try sheet calculate event.
Thanks for the suggestions, but I still can't make it work. Let me specify with the file at hand:

I have made a calender which I want to prepare for next year with the press of a button.

When I run the macro "JaartalVerhogen", the current year will rise by one. This will change the dayname of each day. According to the dayname the columns change color.

Since I have more than 3 colors, I had to use a complicated VBA code (provided by Aquarelle on this website)
which has been put under ThisWorkbook when looking at the vba codes.

Now my problem: The colors will only change when I have selected the sheet and copy paste a formula in the same cell.

I want the colors to change when the macro "JaartalVerhogen" is fired.

The worksheet calculate event seems to be the solution, but how do I use it in this situation?

Please let me know if something is still unclear.

Best regards,
Excelguru Posts 261 Registration date Saturday April 11, 2009 Status Member Last seen June 21, 2011 307
Jul 4, 2009 at 12:44 AM
Hi Trowa,

call the macro in each sheets from the worksheet in which the user enters the data