macro not working on sheet change

[David777]

Afternoon All

I have an excel 2003 workbook with a macro in, the macro changes the colour of a cell based on its value. the macro works up to a point.

on the 1st sheet we have cells all linked to the contents of cells in other sheets
"screen1.jpg"

if I go to another sheet, and change the value from g to X the cell turns black, which is what the macro is meant to do.
"screen2.jpg"

if we then go back to the 1st sheet excel notices that the value has changed from G to X but has made the background white?
if I manually type in X it goes black,

Does anyone know anything I could be doing wrong here?

the macro is:

Please Login or Register  to view this content.

thanks in advance for any replies

[Fettertiger]

Hi David777,

the tricky thing with events is that often they are switched off at the beginning of macros, and switched on again at the end. If the macro fails (or is stopped by the user) the event handler remains switched of.

Maybe you should run this simple macro before trying yours again:

Please Login or Register  to view this content.

Regards
Theo

[Mordred]

You should turn events off but I'm not sure if that should be done with each case or not.try:

Please Login or Register  to view this content.

[David777]

afternoon all

thankyou for the quick replies

unfortunately none of the suggestions worked

FetterTiger - this didnt seem to make any difference
Mordred - this stopped the macro working completely

Any other ideas would be greatly appreciated

regards

[royUK]

To best describe or illustrate your problem you would be better off attaching a dummy workbook, the workbook should contain the same structure and some dummy data of the same type as the type you have in your real workbook - so, if a cell contains numbers & letters in this format abc-123 then that should be reflected in the dummy workbook.

If needed supply a before and after sheet in the workbook so the person helping you can see what you are trying to achieve.

Doing this will ensure you get the result you need!

[David777]

Evening all

I've made a sample worksheet to display the error

the cells in sheet 1 are linked to the cells in sheet 2,
however if you change a value in sheet 2 it correctly changse the value in sheet1 but doesnt run the macro to change the cell colour on sheet 1

any ideas?

regards

[David777]

If there anything in excel that forces a macro to run every few seconds? that would be a way of getting what im after

regards

Dave

[royUK]

Look at the Ontime method

[Fettertiger]

Hi David777,

the reason for the "strange" behavour is that the "WorksheetChange" event is not triggered by changes done through formulas. What you could use instead is the "WorksheetCalculate" Event. Unfortunately the WorksheetCalculate does not know the "Target", therefore you might need to define the range of cells, on which the macro should act on manualy. You can see how this is done in the sample code below:

Please Login or Register  to view this content.

Hope that helps
Regards
Theo
__________________
Please consider:

Thanking those who helped you. Click the scales icon in the upper right corner of the contributor's post and add Reputation.
Cleaning up when you're done. Mark your thread [SOLVED] if you received your answer.