Clear Contents on Update when other code exists in the Worksheet_Change thingy

[JRS_]

Hi everyone,

I have a data collection spreadsheet where Column H uses a data validation list to restrict the values a user can select. Column I then populates a drop down list based on the selection in column H using VB in the Private Sub Worksheet_Change(ByVal Target As Range) area. I would like Column I and Column J to clear contents when Column H is updated, but I can't figure out how to make that happen. I don't really know VB and have borrowed code to make the magic happen so far.

I've attached a workbook with what I've got so far as I'm generally terrible at describing what I'm trying to do. You may notice that the worksheet includes the same scenario with Columns K, L, & M too.

I think I need to add some code in the Private Sub Worksheet_Change(ByVal Target As Range) to .ClearContents or something like that, but I can't find the right spot to stick the code in and/or I am not using the correct command to clear contents only when Column H is updated.

Thanks for your help and sorry for not knowing all the right terminology!

[nilem]

perhaps this will help

Please Login or Register  to view this content.

[JRS_]

Wow. 1) I am in awe, and 2) Thank You so very much. This is amazing.

In the interest of learning, I hope that you'll be game to answer a few newbie questions...

What does this mean and/or why does this work? newVal = Target.Value: .Undo: oldVal = Target.Value
I've never seen the use of colons after anything other than an "=" to declare a variable.

Also, why is it ok to use "case" in Excel but not in Access?

Thank you again!