Macro to clear cell content if cell on the same row is modified based on a drop down list

Hi,


I created a worksheet being filled in manually by users everyday. It has 5 columns. Column A is manually keyed in (date). Column B3:B200 is populated based on a dropdown list. Column C is formula which we can ignore in this discussion. Column D3:D:200 is populated based on a dropdown list INDIRECT to column B and likewise Column E3:E200 is populated based on a dropdown list INDIRECT to column D.

My problem now is, assuming the 5 columns is populated. If the user modifies any cell in Column B, data in columns D and E will remain and most of the time, it is not related to the INDIRECT list in column B and C, respectively. I can delete the full data in D3:E200 but it will be inefficient because the data may be valid for the next day.

Can anyone help me with a macro so that, if any cells in column B3:B200 is modified, the respective column D and E on the same row should be cleared.

Example, B3 = ABC, D3=1 and E3=2. if B3 is modified, say the user pull down the list and select CBA, the contents of D3 and E3 should be cleared and let the user populate with the new data.

Please help.

Thanks.

Please Login or Register  to view this content.

Hi rcm, thank you for the very quick response. I tested the code but then if I select column B, column D&E is immediately cleared. the clearing of Column D&E should be when the user modifies the entry in column B, not when the cell is selected only.

I believe we're almost there, it's just a simple tweak on the code.

awaiting for the code.... alner

Please Login or Register  to view this content.

My bad. Now it should only work if its modified!!!