Apply VBA code to multiple cells

Hi there,

I'm trying to make a drop-down menu within which multiple answers can be selected. I found some code online that I copy-pasted into my file and it works great, except that it only applies to one cell. I would like to have this multiple-selection enabled dropdown menu for a whole column of cells on my sheet.

Here is the code:

Private Sub Worksheet_Change(ByVal Target As Range)

    Dim oldVal As String
    Dim newVal As String
    
    If Target.Address(0, 0) <> "I4" Then Exit Sub

    On Error GoTo ReEnable
    Application.EnableEvents = False
    newVal = Target.Value
    Application.Undo
    oldVal = Target.Value
    Target.Value = newVal

    If oldVal <> "" And newVal <> "" Then
        Target.Value = oldVal & ", " & newVal
    End If
ReEnable:
    Application.EnableEvents = True
    
End Sub

...where I4 is just one of the cells in which I would want this kind of drop-down menu to appear. I have never used VBA before, so the answer may be obvious to others, but my question is: how can I apply this code to cells other than I4?

Thanks!

If Intersect(Target, Columns("I:I"))  Is Nothing Then Exit Sub

Regards, TMS

@rosenina18

Code Tags Added
Your post does not comply with Rule 3 of our Forum RULES. Use code tags around code.

Posting code between [CODE] [/CODE] tags makes your code much easier to read and copy for testing, it also maintains VBA formatting.

Highlight your code and click the # icon at the top of your post window. More information about these and other tags can be found at http://www.excelforum.com/forum-rule...rum-rules.html



(Because you are new to the forum, I have added them for you today. Please take a few minutes to read all Forum Rules and comply in the future.)

You're welcome.




If you are satisfied with the solution(s) provided, please mark your thread as Solved.


New quick method:
Select Thread Tools-> Mark thread as Solved. To undo, select Thread Tools-> Mark thread as Unsolved.

Or you can use this way:

How to mark a thread Solved
Go to the first post
Click edit
Click Go Advanced
Just below the word Title you will see a dropdown with the word No prefix.
Change to Solved
Click Save


You may also want to consider thanking those people who helped you by clicking on the little star at the bottom left of their reply to your question.

I want to apply the below VB code on the whole column "I " kindly guided please

Private Sub Worksheet_Change(ByVal Target As Range)

' Initializing variables
Dim oldVal As String
Dim newVal As String

' Checking for changes in the drop down lists

Application.EnableEvents = True

If Target.Address = "$I$4" Or Target.Address = "$G$3" Then
    
    If Target.SpecialCells(xlCellTypeAllValidation) Is Nothing Then
        Exit Sub
' Adding the multiple selected items to the list
    Else
        If Target.Value = "" Then
            Exit Sub

        Else
            Application.EnableEvents = False
            newVal = Target.Value
            Application.Undo
            oldVal = Target.Value
            If oldVal = "" Then
                Target.Value = newVal
            Else
                If InStr(oldVal, newVal) = 0 Then
                    Target.Value = oldVal & ", " & newVal '& vbNewLine &
                Else
                    Target.Value = oldVal
                End If
            End If
        End If
    End If
End If

Application.EnableEvents = True

End Sub

Administrative Note:

Welcome to the forum.

We are happy to help, however whilst you feel your request is similar or even the same as this thread, we have a rule that you open your own thread on the issue and do not piggy back another member's thread.

Please see Forum Rule #1 about hijacking and start a new thread for your query.

If you are not familiar with how to start a new thread see the FAQ: How to start a new thread