Using VBA to Conditionally Overwrite Multiple Cells Based on Dropdown

[keithmcvean]

I have a spreadsheet (G4:T1000) with a dropdown list in column G. What I'm trying to do, using VBA, is to lookup the value of G in a table (ProgramTemplate) and fill in the columns H:T for that row based on that table.

I could do this with a simple VLOOKUP but I need the ability to have the users overwrite the data in columns H:T should they choose the value "custom" in column G.

Ideally, if the user decided for some reason to choose custom and then decided again that they instead wanted a different value in G, the custom data they entered would be overwritten with the values from the table.

I can figure out how to do this every time the value in the sheet changes but I was hoping for something more row by row for efficiency sake if nothing else.

 

[ranman256]

In the worksheet code you can add this when you change the value in col G:

Private Sub Worksheet_Change(ByVal Target As Range)
If ActiveCell.Column = 7 Then
    MsgBox "update H thru T values", , "col 7 change"
End If
End Sub