How to use Target Address for multiple rows in VBA?

Question

Need your guidance on how If i can apply Target Address & its Value for multiple rows. I am new to VBA, The below code is working for 1 Row as of now, but how can i can apply for multiple rows.

Let's say, In a range from B4 to B10, if i select B7, then C7 & D7 should give " Please Select" As of now its working for row B4 row only.

How it can be used for multiple rows. Could you please guide me on this.

VB Code

Private Sub Worksheet_Change(ByVal Target As Range)

If Target.Address = "$B$4" And Target.Value = "AA1" Then
Range("C4").Value = "Please Select"
Range("D4").Value = "Please Select"

ElseIf Target.Address = "$B$4" And Target.Value = "BB1" Then
Range("C4").Value = "Please Select"
Range("D4").Value = " "

ElseIf Target.Address = "$B$4" And Target.Value = "CC1" Then
Range("C4").Value = "Please Select"
Range("D4").Value = " "

ElseIf Target.Address = "$B$4" And Target.Value = "DD1" Then
Range("C4").Value = "Please Select"
Range("D4").Value = " "

ElseIf Target.Address = "$B$4" And Target.Value = "EE" Then
Range("C4").Value = "Please Select"
Range("D4").Value = " "

ElseIf Target.Address = "$B$4" And Target.Value = "" Then
Range("F4").Value = ""
Range("D4").Value = " "

End If
End Sub

Target Address & Conditional Data Validation

Tim Williams · Answer 1 · 2021-09-02T20:10:46.643

It's worth noting that Target may be a multi-cell range - it's not guaranteed to be a single cell - so you need to allow for that possibility in your code.

Select Case would be useful here:

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim rng As Range, c As Range
    
    Set rng = Application.Intersect(Target, Me.Range("B4:B10"))
    If rng Is Nothing Then Exit Sub 'nothing to do...
    'loop over any affected cells in the range of interest
    For Each c In rng.Cells
        Select Case c.Value
            Case "AA1"
                c.EntireRow.Columns("C").Resize(0, 2).Value = Array("Please Select", "Please Select")
            Case "BB1", "CC1", "DD1", "EE"
                c.EntireRow.Columns("C").Resize(0, 2).Value = Array("Please Select", " ")
            Case ""
                c.EntireRow.Columns("C").Resize(0, 2).Value = Array("", " ")
        End Select
    Next c
End Sub

score 0 · Answer 2 · answered Sep 02 '21 at 05:32

Try

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim r As Long
    If Target.Row > 3 And Target.Column = 2 Then
        r = Target.Row
        If Target.Value = "AA1" Then
            Range("C" & r).Value = "Please Select"
            Range("D" & r).Value = "Please Select"
        ElseIf Target.Value = "BB1" Then
            Range("C" & r).Value = "Please Select"
            Range("D" & r).Value = " "
        ElseIf Target.Value = "CC1" Then
            Range("C" & r).Value = "Please Select"
            Range("D" & r).Value = " "
        ElseIf Target.Value = "DD1" Then
            Range("C" & r).Value = "Please Select"
            Range("D" & r).Value = " "
        ElseIf Target.Value = "EE" Then
            Range("C" & r).Value = "Please Select"
            Range("D" & r).Value = " "
        ElseIf Target.Value = "" Then
            Range("F" & r).Value = ""
            Range("D" & r).Value = " "
        End If
    End If
End Sub

How to use Target Address for multiple rows in VBA?

2 Answers2