How to let VBA code work over entire column?

[firexcelsior]

Hello ?

I have a VBA code which allows for multiple selections from a drop down list:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim Oldvalue As String
Dim Newvalue As String
Application.EnableEvents = True
On Error GoTo Exitsub
If Target.Address = "$I$3" Then
If Target.SpecialCells(xlCellTypeAllValidation) Is Nothing Then
GoTo Exitsub
Else: If Target.Value = "" Then GoTo Exitsub Else
Application.EnableEvents = False
Newvalue = Target.Value
Application.Undo
Oldvalue = Target.Value
If Oldvalue = "" Then
Target.Value = Newvalue
Else
If InStr(1, Oldvalue, Newvalue) = 0 Then
Target.Value = Oldvalue & ", " & Newvalue
Else:
Target.Value = Oldvalue
End If
End If
End If
End If
Application.EnableEvents = True
Exitsub:
Application.EnableEvents = True
End Sub

The code works just fine but my only problem is that it only runs on one cell (Cell I3).

What should I change in Target.Address = "$I$3" to make it run over the entire table column (from I3 to I1639)?

Thank you!

 

[bobsan42]

replace this row:

If Target.Address = "$I$3" Then

with these two:

    Const rngA = "I3:I1639"
    If Not Intersect(Target, Target.Parent.Range(rngA)) Is Nothing Then

 

[Akuini]

Try replacing this:

If Target.Address = "$I$3" Then

with this:

If Target.Cells.CountLarge > 1 Then GoTo Exitsub
    If Not Intersect(Target, Range("I1:I1639")) Is Nothing Then

 

[firexcelsior]

replace this row:

If Target.Address = "$I$3" Then

with these two:

    Const rngA = "I3:I1639"
    If Not Intersect(Target, Target.Parent.Range(rngA)) Is Nothing Then

Thanks, works perfectly!

 

[bobsan42]

glad we could help