How to use a range for target address in VBA

[albert_de]

I have a worksheet that has Dependant Dropdown Lists in it (Column J & K). Importantly, the same lists in Column J & K repeat in the rows below.

I am trying to get the Dropdown List in Column K to display "Select Name>>" whenever the Column J Dropdown List is changed.
And ideally for a cell in Column K to remain blank if the adjacent cell in Column J is blank.

I have found the following VBA instruction on how to do this, and it works perfectly for me, but of course it will only affect Row 16 and none of the rows below it (e.g. Range J16:J200 or K16:K200).

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$J$16" Then
Range("K16").Value = "Select Name>>"
End If
End Sub

How do I modify this VBA code so that the:

- Dropdown List in Column K displays "Select Name>>" whenever the Column J Dropdown List is changed
- cell in Column K remains blank if the adjacent cell in Column J is blank.

Thank you for any help that you can offer a non VBA mastermind.

 

[Akuini]

Try:

Private Sub Worksheet_Change(ByVal Target As Range)

If Target.Cells.CountLarge > 1 Then Exit Sub
    If Not Intersect(Target, Range(" J16:J200")) Is Nothing Then
        
        Application.EnableEvents = False
        If Target = "" Then
            Target.Offset(0, 1) = ""
        Else
            Target.Offset(0, 1) = "Select Name>>"
        End If
        Application.EnableEvents = True
        
    End If

End Sub

 

[albert_de]

Hi Akuini

Thank you for your very prompt response to my question.
Your code works perfectly and exactly as requested in my post. You have saved me days!
Thank you

 

[albert_de]

Hi Akuini

Thank you for your very prompt response to my question.
Your code works perfectly and exactly as requested in my post. You have saved me days!
Thank you

 

[Akuini]

Hi Akuini

Thank you for your very prompt response to my question.
Your code works perfectly and exactly as requested in my post. You have saved me days!
Thank you

You're welcome, glad to help & thanks for the feedback.
Note:
The code is designed to handle changes in individual cells only, so it won't work when you copy multiple cells at once to the specified range. If that is a possible scenario, then we need to amend the code.

 

[albert_de]

Thank you again Akuini. At this stage I don't think that I will be copying multiple cells at once to the specified range. But should I find that I need to do this I will repost here.
How good is Excel!