Applying VBA to multiple range

[abesacto916]

Hey all,

I'm trying to teach myself VBA and this forum has been extremely useful. I've been using the code below, which I found online. The only issue I have is applying the code to from B2:B100. I juts cannot seem to have it apply to all the cells and I'm lost. I created a Data Validation List Box and this code allows me to select multiple choices, but I need this to occur from B2:B100.

Help!!!!!

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)

'Code by Sumit Bansal from Welcome to Trump Excel » Trump Excel
' To Select Multiple Items from a Drop Down List in Excel

Dim Oldvalue As String
Dim Newvalue As String

On Error GoTo Exitsub
If Target.Address = "$B$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
Target.Value = Oldvalue & ", " & Newvalue
End If
End If
End If
Application.EnableEvents = True

Exitsub:
Application.EnableEvents = True

End Sub

 

[John_w]

Replace the $B$3 line with:

    If Not Intersect(Target, Range("B2:B100")) Is Nothing Then

Please use CODE tags when posting VBA code - the # icon in the message editor.

 

[abesacto916]

Awesome, that worked. What would be the best way to omit B50 from being included and then recommencing on B51?

I'll be sure to use the CODE tags next time.

Thanks for your help, it's greatly appreciated!

 

[My Aswer Is This]

You may try this:
But be aware sheet change events can be tricky and may result in some looping problems.
I'm not a big fan of anything that happens automatically unless I'm really sure what I'm doing.

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("B2:B100")) Is Nothing Then

 

[John_w]

What would be the best way to omit B50 from being included and then recommencing on B51?

    If Not Intersect(Target, Union(Range("B2:B49"), Range("B51:B100"))) Is Nothing Then