How to Apply VBA Macro Code to Multiple Columns

[rlovestheland]

Hello! I have this code below applying to one column right now so that I can select multiple keywords from a data validation drop down. I need this to apply to multiple columns. I found a similar query on here but I couldn't get the code to work for me. Please help, see below.

I want it to work for column counts 3, 4, 5, 6, 7, 9, 10, 11 and 13 (C, D, E, F, G, I, J, K, M)

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
Dim rngDV As Range
Dim oldVal As String
Dim newVal As String
Dim lUsed As Long
If Target.Count > 1 Then GoTo exitHandler


On Error Resume Next
Set rngDV = Cells.SpecialCells(xlCellTypeAllValidation)
On Error GoTo exitHandler


If rngDV Is Nothing Then GoTo exitHandler


If Intersect(Target, rngDV) Is Nothing Then
'do nothing
Else
Application.EnableEvents = False
newVal = Target.Value
Application.Undo
oldVal = Target.Value
Target.Value = newVal
If Target.Column = 3 Then
If oldVal = "" Then
'do nothing
Else
If newVal = "" Then
'do nothing
Else
lUsed = InStr(1, oldVal, newVal)
If lUsed > 0 Then
If Right(oldVal, Len(newVal)) = newVal Then
Target.Value = Left(oldVal, Len(oldVal) - Len(newVal) - 2)
Else
Target.Value = Replace(oldVal, newVal & ", ", "")
End If
Else
Target.Value = oldVal _
& ", " & newVal
End If

End If
End If
End If
End If


exitHandler:
Application.EnableEvents = True
End Sub

 

[tusharm]

If I understand correctly, replace the

If Target.Column = 3 Then

with

    Select Case target.Column
    Case 3, 4, 5, 6, 7, 9, 10, 11, 13

and appropriate changes to the corresponding else and end if as in

    Case Else
        End Select

Hello! I have this code below applying to one column right now so that I can select multiple keywords from a data validation drop down. I need this to apply to multiple columns. I found a similar query on here but I couldn't get the code to work for me. Please help, see below.

I want it to work for column counts 3, 4, 5, 6, 7, 9, 10, 11 and 13 (C, D, E, F, G, I, J, K, M)

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
Dim rngDV As Range
Dim oldVal As String
Dim newVal As String
Dim lUsed As Long
If Target.Count > 1 Then GoTo exitHandler


On Error Resume Next
Set rngDV = Cells.SpecialCells(xlCellTypeAllValidation)
On Error GoTo exitHandler


If rngDV Is Nothing Then GoTo exitHandler


If Intersect(Target, rngDV) Is Nothing Then
'do nothing
Else
Application.EnableEvents = False
newVal = Target.Value
Application.Undo
oldVal = Target.Value
Target.Value = newVal
If Target.Column = 3 Then
If oldVal = "" Then
'do nothing
Else
If newVal = "" Then
'do nothing
Else
lUsed = InStr(1, oldVal, newVal)
If lUsed > 0 Then
If Right(oldVal, Len(newVal)) = newVal Then
Target.Value = Left(oldVal, Len(oldVal) - Len(newVal) - 2)
Else
Target.Value = Replace(oldVal, newVal & ", ", "")
End If
Else
Target.Value = oldVal _
& ", " & newVal
End If

End If
End If
End If
End If


exitHandler:
Application.EnableEvents = True
End Sub

 

[rlovestheland]

If I understand correctly, replace the

If Target.Column = 3 Then

with

    Select Case target.Column
    Case 3, 4, 5, 6, 7, 9, 10, 11, 13

and appropriate changes to the corresponding else and end if as in

    Case Else
        End Select

Where in the end if series do I include the Case Else, End Select? I can't seem to get the loop to close correctly. Thank you!!