Worksheet_Change procedure when multiple cells are involved

[anandvh]

In my application, I have the following requirement:

If there is a change in cell D10 and the value is "COMPANY" then the text colors of certain cell should change to red

OR

If there is a change in cell E23 and the value is "YES" then the text colors of certain cells should change to red

OR

If there is a change in cell E34 and the value is "YES" then the text colors of certain cells should change to red

The cells in which the color should change to red are different in each of the three conditions above.

I am able to do it for change in one of the cells (either D10, E23 or E34) but not not able to modify the code to cover any of the three conditions. The code I have used for change in one cell is given below:

Please Login or Register  to view this content.

Can someone help modifying this code to cover any of the three conditions. If a completely different code is required, that is also accepted.

Regards,

Anandvh

[TMS]

Please Login or Register  to view this content.

Regards, TMS

[Leith Ross]

Hello anandvh,

If Range("I10:L10") is associated with D10, then what ranges are associated with E23 and E34? Please provide complete information.

[mperrah]

something like this?
(not tested)

Please Login or Register  to view this content.

[TMS]

This

Please Login or Register  to view this content.

will exit the Change event handler if one of the other cells is changed. You probably need to make a union of rng1, rng2 and rng3 and test that.

Regards, TMS

[anandvh]

TMS - Thanks a ton for the quick solution. The code you gave has been tested and is working like a dream. I will have to study the code you sent to understand the differences with the various options I was trying. Clearly I have a long way to go in getting a hand of programming. Your second response has been ignored since I had already tried out a code similar to what mperrah has provided but did not work

mperrah - Thanks for the response. The code you have sent was one of the versions that I was trying and failing. None of the text in any of the cells changed color. Thanks anyway for your efforts.

[TMS]

You're welcome.

Please Login or Register  to view this content.

You don't actually need to disable event handling as you're not changing the values, just the formatting.


Regards, TMS