Clearing cells based upon changed cells

[reichertc]

Good day all.

I'm trying to come up with some VBA code that will do the following:

1) If the value of cell Y13 changes, clear the contents of cells N13 and AC13
2) If the value of cell Y14 changes, clear the contents of cells N14 and AC14
3) Etc. all the way up to cels Y34, N34, and AC34

I'm sure there is a relatively simple and elegant way to do this that does not involve writing a macro for each cell...which is what I started to do as follows:

Please Login or Register  to view this content.

Any help would be appreciated!

[StephenR]

Try this:

Please Login or Register  to view this content.

[reichertc]

Thanks StephenR

That appears to correctly clear column "N", but it isn't working for column AC. I am getting "Run-time error '1004': Cannot change part of a merged cell."

I did not mention... my cells Y13 through Y34 are merged across through column AB. (For example, Y13 is merged with Z13, AA13, and AB13).

[StephenR]

There's a lesson - don't use merged cells. In any case do you mean AC is merged?

[reichertc]

Unfortunately, in order to make this spreadsheet presentable, merged cells are a necessary evil. (The cells contain data validation/drop down pick lists, and I need the cell wide enough to be able to view what the user is picking.)

AC is not merged..Y is merged with Z, AA, and AB.

I've uploaded the spreadsheet so you can see what is going on. The sheet that I want this to work on is "LINE CLASS" The intent is that when the information in the "Material" column (merged cells Y:AB) is changed, the values in the "Ej" and "Form" column will clear, and force the user to reselect them.

Try not to laugh too hard at the mess that this spreadsheet is...I'm taking something that was developed by others and trying to improve it and I'm only halfway there.

[StephenR]

You'd be better off adjusting the column width. Anyway, good luck with that!

Change the 4 in the second offset to 1.

[reichertc]

Excellent, worked like a charm!

[reichertc]

StephenR, I'm taking your advice and reorganizing the sheet to get rid of merged cells within my formula areas...the solution above will provide an error if a user selects multiple (merged) cells and tries to delete them, or attempts to copy a value to multiple merged cells. I considered adding a bit of code to prevent users from selecting more than one cell, but decided that impaired the functionality of the sheet too much.

Thanks again!