Technical Q: worksheet_change event

[marspu]

Hi - I am using the worksheet_change event to change the background colour of cells (based on the MrExcel Office VBA Macros book). When the values are typed in manually eveything works fine....but I am populating the cells using an index function which links to another spread and when I change the value in the linked spread, whilst the value changes here correctly, the colour (triggered by the worksheet_change) doesn't. Is there an alternative event I can use so I don't have to update hundreds of cells manually?

Thanks in advance for your help - Mark

 

[VoG]

You could try using Worksheet_Calculate(). This doesn't have a Target parameter so you would possibly need to loop through the range of interest.

 

[marspu]

Thanks for the quick response! When you say 'loop thru the range of interest '- if multiple cells are being changed as a result of the links will this trigger one event for all of them which I then need to loop thru by defining a target range (since this event doesn't use one) or would they trigger separate calculate events?

 

[VoG]

When the sheet calculates it triggers a single Worksheet_Calculate event.

 

[marspu]

Brilliant - it worked a treat! Thanks