Thread: automatically change selected values in column Y when values in Column U change

Problem:
Using Excel 2007, how I can automatically change selected values in column Y when values in Column U change? For example (using example data from another user):

Cell U2 is a validation list with options: Boat/Car

Cell Y2 is a validation list where if U2 = Boat the options are Catamaran/Sailboat/Tugboat. If U2 = Car the options are Convertible/Sedan/Midsize.

The problem is that if Y2 is preselected as Sailboat, for example, and you go back and change U2 to equal Car, cell Y2 remains as Sailboat - an option from the Boat validation list. I would like it to automatically update to being "-" as the 1st item on the car validation list. I also need it to happen for all cells in the U and Y columns.
So starting values of sheet are:
U2 = Boat Y2 = Sailboat
U3 = Car Y3 = Sedan
U4 = Boat Y4 = Catamaran


If I change U2 = Car, I want Y2 = "-" (which is the first option in the Car validation list.
If I change U3 = Boat, I want Y3 = "-" (which is the first option in the Boat validation list.
If I change U4 = Car, I want Y4 = "-" (which is the first option in the Car validation list.
and so on ... for the cells in columns U and Y.

I have tried to use the solution posted by lenze as follows:

Private Sub WorkSheet_Change(ByVal Target as Range)
If Target.Address = "$U$2" Then Range("$Y$2").ClearContents
End Sub

...but although this works for the first row with the cells of U2 and Y2, I can't get it to work for the rest of the cells even though I have tried dragging the cell Y2 down the Y column and tried using the format Painter button down the Y column.

Can anyone help?

Thanks
RainingAgain

One way:

Private Sub WorkSheet_Change(ByVal Target as Range)
Dim Cell As Range

Application.EnableEvents = False
For Each Cell In Target
    If cell.Column = 21 Then Cells(cell.Row, 25).ClearContents
Next Cell
Application.EnableEvents = True

End Sub

Try:

Private Sub WorkSheet_Change(ByVal Target As Range)
If Target.Row = 1 Then Exit Sub
If Target.Column = "$U" Then
    Application.EnableEvents = False
    Target.Offset(0, 4).ClearContents
    Application.EnableEvents = True
End If
End Sub

Please note that, according to forum rules, any code examples should be enclosed in Code Tags.

Regards

This:

If Target.Column = "$U" Then

might need to be changed to:

If Target.Column = 21 Then

Apologies, TMS

To target an individual, it is common practice to use "@", for example, @TMS.

My version

Private Sub WorkSheet_Change(ByVal Target As Range)
If Target.Row = 1 Then Exit Sub
If Target.Column = 21 Then
    Application.EnableEvents = False
    Target.Offset(0, 4).ClearContents
    Application.EnableEvents = True
End If
End Sub

The first line defines the Change Event which is passed a "Target" range; this may be one or more cells, rows or columns. You can refer to the Target cell and its properties using the range name "Target"

The first line of code checks the row of the Target cell. If it is 1, it exits the routine ... this avoids corrupting the header row.

The next line of code then checks if the cell being changed is in column 21 (U). If it is, it switches off Events so that the code won't be actioned twice, clears the cell 4 columns away, and switches Event handling back on again.

Hope this clarifies.

Regards

Many people do abort the ws-change macros if more than one cell is changed at a time, I prefer not to do that. My version will analyze all the cells changed at once and consider them all individually.

All my sub is doing is check to see if the "cell" changed was in column 21 (col U), and if so, empty column 25 (Y) of that same row.

Private Sub WorkSheet_Change(ByVal Target as Range)
Dim Cell As Range

Application.EnableEvents = False
For Each Cell In Target     'process each changed cell one at a time
  'if the cell is in column U, then empty column Y of the same row
    If cell.Column = 21 Then Cells(cell.Row, 25).ClearContents
Next Cell
Application.EnableEvents = True

End Sub