VBA to delete cell contents based upon another

[Legacy 48831]

Hello everyone. I have been searching through the forums looking for an example to delete the contents of a cell based upon the value in another but cannot seem to get it to work.
What I have is a spreadsheet that in A15 if it is blank, I would like to have E15 to delete it's contents. If A15 is anything else then I want the user to be able to enter a number in E15, otherwise if they clear A15 then E15 clears.

The major issue is that I need this to work not only on A15 and E15 but from A15:A43 and from E15:E43.

Is there a simple method of doing this with VBA, since it would be only clearing a range within the column instead of a full column?

 

[Legacy 48831]

Nothing. If I add any data in A16 & E16 then clear A16, the value stays in E16. I haven't tried to insert a row in this new spreadsheet since the cell is not clearing, adding a row is pointless. I must be doing something wrong. Thinking a restart of my computer may be of some help at this point.

 

[Legacy 48831]

I have replaced the code with what offthelip had posted so I could make sure it was still at least working as it was. It does in fact clear the E cell when the A cell is cleared. I cannot insert a row without the runtime error 1004 due to it stopping at

Target.Offset(0, 4) = ""

 

[Yongle]

Ah... oops ... a bit of a typo on my part

If Target.CountLarge > [COLOR=#ff0000]1[/COLOR] Then Exit Sub

The above line counts the number of cells being changed (lots when a row being inserted) and prevents the error you experienced
BUT the hurdle should allow for 1 cell being changed - for a very obvious reason the sub never kicks in if hurdle set at ZERO !!


If you want user to be allowed to clear several cells at once, then this should work without the code failing when rows are inserted

If Target.CountLarge > [COLOR=#ff0000]1[/COLOR][COLOR=#ff0000]00[/COLOR] Then Exit Sub

 

[Legacy 48831]

Hi Yongle, great news. That took care of the issue!! I was really confused as to what was wrong. I am thankful you stuck with me on this issue.
I will keep testing to make sure I didnt miss anything but I think all is well now.