Automatic Refresh / Recalculation

[Jay Walsh]

Hi All,
I am working on a risk register and have created some VBA formula to colour the cells dependent on the level of risk (I have 5 levels of risk therefore need VBA as opposed to conditional formatting). The cells that contain the level of risk contain formula that are dependant on other "Input"cells - specifically I have Column A (titled "Probability"), it has a drop down list to choose from; and column B (titled "Impact") another drop down list to choose from; column C contains a formula that has an INDEX & MATCH formula to calculate the score from columns A and B (looking up a separate table); then column D contains the INDEX and MATCH formula that returns the Risk Level dependant on column C's score (ie the one I want the colour to change in. The code I have put in works in the first instance when a risk is first entered and the risk is calculated from the "Input" cells (A&B). However, when the "input" cells are changed (hence change the level of risk in the risk cells), the formula works OK to change the formula result (eg "Critical" changes to "Severe"), but the cell colour does not automatically update with it! It only works if I double click the cell. Does anyone know how this can automatically update?
Cheers
Jay
The following is the codecode:


Code:

Please Login or Register  to view this content.

[blane245]

The Worksheet_Change Event is only activated on cells that are changed by value and not when calculations occur. You can see this by putting a debug breakpoint in your routine and seeing when the Change event occurs and what the Target value is. If you know what cells will contain the risk values, then you can use the Calculate Event to adjust the colors after all calculations are complete.