Clear Contents of cell of other cell is not equal to a value

[stevemills04]

How do I clear the contents of the cell within range K3:K300 if the data in cell J is not equal to "Promotable"? Obviously, if cell J3 is not Promotable then cell K3 should be cleared, etc...

I am not very good with VBA but tried a few things and failed.

Thank you in advance.

[AB33]

Please Login or Register  to view this content.

[stevemills04]

Thanks AB33 but this did not work. I copied and pasted the code into the worksheet code. I changed J to Promotable and selected a value in K. When I then changed J to a value other than Promotable nothing happened to K.

[AB33]

Nothing happens because it is not event driven code. You have to put the code on standard module and run it.

[stevemills04]

Makes sense, is there a quick/easy way to make it event driven? It should run anytime column J's value is changed.

[AB33]

Put this in your worksheet

Please Login or Register  to view this content.

[HaHoBe]

Hi, AB33,

if you limit the availabiiity of the macro to a cell within a range (any altering of two or more cells should raise an error) why do you call a macro that covers the whole range? I´d preferred to just have the row updated:

Please Login or Register  to view this content.

Ciao,
Holger

[stevemills04]

Hi, AB33,

if you limit the availabiiity of the macro to a cell within a range (any altering of two or more cells should raise an error) why do you call a macro that covers the whole range? I´d preferred to just have the row updated:

Please Login or Register  to view this content.

Ciao,
Holger

HaHoBe, thank you for assisting. Is this event driven? I need it to run anytime column J is changed.

[HaHoBe]

Hi, steve,

yes, it´s the event that will be triggered if any entry or change is made in the worksheet. So the code must go behind the worksheet which should be monitored (right-click on the worksheet-Tab, choose View Code, paste the code into the code-window). As any change would start the macro the range for which the code should work is narrowed down to Range J3:J300.

Please do not quote whole posts - please feel free to have a look at the Forum Rules for further information.

Ciao,
Holger