Launching macros automatically

[Lookout_Joe]

I have written a macro to change the color of individual cells in a matrix based on the cell value. In this case, the cell values are vendor names, and I'd like the cell background to automatically change based on the formula results which indicate the best vendor.

I tried the conditional formatting thing, but it only supports 3 conditions, and I have many more potential vendors (cell values) than that.

I'd like the matrix colors to change when the user makes a chage/entry to the spreadsheet. In the past I've put a button on the worksheet for the user to click in order to update the formatting, but I'd like something more elegant if possible.

Any thoughts?

 

[Juan Pablo González]

Put it in the Worksheet_Change() event and determine if any of the dependant cells (The ones that determine if a new color is needed) has changed (Using Intersect and Target), or, use the Worksheet_Calculate() event. This option I DO NOT recommend, i think it would make the workbook terribly slow !

 

[Lookout_Joe]

I opted for the Worksheet_Calculate() option on the worksheet in question since the vendor names I mentioned are selected by formulas. My research indicated that the Worksheet_Change() event wouldn't fire if the cell contents changed as a result of forumala output. The Worksheet_Calculation() solution works, but as you suspected has slowed the workbook down a little, but not too much.

Many thanks for your help.