Launching macros automatically

Lookout_Joe · Feb 22nd, 2002, 12:46 PM

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 · Feb 22nd, 2002, 08:17 PM

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 · Mar 6th, 2002, 12:23 PM

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.

Feb 22nd, 2002, 08:17 PM	#2
Juan Pablo González MrExcel MVP Join Date: Feb 2002 Location: Bogota, Colombia Posts: 11,927	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 ! __________________ Regards, Juan Pablo González http://www.juanpg.com

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

Feb 22nd, 2002, 12:46 PM	#1
Lookout_Joe New Member Join Date: Feb 2002 Posts: 2	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?

Mar 6th, 2002, 12:23 PM	#3
Lookout_Joe New Member Join Date: Feb 2002 Posts: 2	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.