Run Conditional Format Macro without direct cell input

[statenja]

Hello, attached is an example of a file that is used to track status of multiple teams. Each team has a tab and a macro that changes cell color based on direct cell input. The 3rd tab in this spreadsheet is used to quickly roll up all of the teams into one sheet. The cells values are references to the cells in each of the individual tabs.

The problem I am having is making the formating apply in the rollup tab. It is set up to look for direct cell input to run the macro, but that isn't how the rollup worksheet is updated. What's the best way to modify this code to make it work?

Here is the code for the rollup worksheet.

Please Login or Register  to view this content.

[statenja]

I did find a way to just record a macro to copy and paste the cells back over themselves and then associated that macro with an action button. It would be nice to not have to use a button at all.

[foxguy]

Hi statenja;

Have it triggered by the Worksheet_Calculate(), instead of the Worksheet_Change().

[statenja]

I tried changing the Worksheet_Change to Worksheet_Calculate and got a compile error because the procedure declaration does not match description of event or procedure having the same name. How should I change the target range to resolve the error?

Please Login or Register  to view this content.

[foxguy]

Hi statenja;

There is no "Target" in the Worksheet_Calculate(). The easiest way to make sure you have the syntax correct:

In the window where Worksheet_Change() is, there are 2 dropdown boxes at the top. Make sure the left one says "Worksheet", and then select "Calculate" in the right box. That will create an empty macro that has the correct words for the macro name.

Then just cut and paste the code from the old macro into the correct one. And be sure and delete the old one, otherwise Excel will get confused because there will be 2 macros with the same name.