How to loop the VBA Code

[AlaaEddin]

Hello There, I have the following Code and I want to loop through the following Code:

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Address = "$B$12" Then
        Range("C12").Value = "Please Select..."
    End If
    If Target.Address = "$C$12" Then
        Range("D12").Value = "Please Select..."
        Range("E12").Value = "Please Select..."
        Range("F12").Value = "Please Select..."
        Range("G12").Value = "Please Select..."
        Range("H12").Value = "Please Select..."
    End If
    
    If Target.Address = "$B$13" Then
        Range("C13").Value = "Please Select..."
    End If
    If Target.Address = "$C$13" Then
        Range("D13").Value = "Please Select..."
        Range("E13").Value = "Please Select..."
        Range("F13").Value = "Please Select..."
        Range("G13").Value = "Please Select..."
        Range("H13").Value = "Please Select..."
    End If

End Sub

The idea of the code that if data validation list value changed the other cells whom are related to that list will replace what is inside it to "Please Select..." to start new selection so instead of copy and paste the code for 20 rows I need to loop it.

Regards.

 

[Akuini]

I don’t quite understand what you want.
If B12 is changed then C12:H12 will have "Please Select..."
If C12 is changed then D12:H12 will have "Please Select..."
& you want the same thing for row 12-31 (20 rows)
Is that correct?

Try this:

Private Sub Worksheet_Change(ByVal Target As Range)

    If Not Intersect(Target, Range("B12:C31")) Is Nothing Then
        
        If Target.Cells.Count = 1 Then
        Application.EnableEvents = False
            If Target.Column = 2 Then
            Target.Offset(, 1) = "Please Select..."
            ElseIf Target.Column = 3 Then
            Target.Offset(, 1).Resize(, 5) = "Please Select..."
            End If
        
        Application.EnableEvents = True
        End If
       
    End If

End Sub

 

[Kamolga]

You can select a bigger target (in my example rows 12-32) and apply it to the row instead of looping:

Private Sub Worksheet_Change(ByVal Target As Range)
 [COLOR=#006400] 'Change in B column[/COLOR]
    If Not Intersect(Target, Range("[COLOR=#ff0000]B12:B32[/COLOR]")) Is Nothing Then
        Range("C" & Target.Row).Value = "Please Select..."
    End If
[COLOR=#006400]  'Change in C column[/COLOR]
    If Not Intersect(Target, Range("[COLOR=#ff0000]C12:C32[/COLOR]")) Is Nothing Then
         Range("D" & Target.Row).Value = "Please Select..."
         Range("E" & Target.Row).Value = "Please Select..."
         Range("F" & Target.Row).Value = "Please Select..."
         Range("G" & Target.Row).Value = "Please Select..."
         Range("H" & Target.Row).Value = "Please Select..."
    End If
End Sub

In this case, change in column B modify value in C which trigger the second part of the event. You can escape using 'GoTo' or 'End Sub'.

 

[AlaaEddin]

I don’t quite understand what you want.
If B12 is changed then C12:H12 will have "Please Select..."
If C12 is changed then D12:H12 will have "Please Select..."
& you want the same thing for row 12-31 (20 rows)
Is that correct?

Try this:

Private Sub Worksheet_Change(ByVal Target As Range)

    If Not Intersect(Target, Range("B12:C31")) Is Nothing Then
        
        If Target.Cells.Count = 1 Then
        Application.EnableEvents = False
            If Target.Column = 2 Then
            Target.Offset(, 1) = "Please Select..."
            ElseIf Target.Column = 3 Then
            Target.Offset(, 1).Resize(, 5) = "Please Select..."
            End If
        
        Application.EnableEvents = True
        End If
       
    End If

End Sub

thank you so much this is exactly what I am looking for.

 

[Akuini]

You're welcome, glad to help, & thanks for the feedback.

 

[Akuini]

Sorrry, the above code will change only C12 if B12 changes. If you want it to change C12:H12 then use this one instead:

Private Sub Worksheet_Change(ByVal Target As Range)

    If Not Intersect(Target, Range("B12:C31")) Is Nothing Then
        
        If Target.Cells.Count = 1 Then
        Application.EnableEvents = False
            If Target.Column = 2 Then
            Target.Offset(, 1).Resize(, 6) = "Please Select..."
            ElseIf Target.Column = 3 Then
            Target.Offset(, 1).Resize(, 5) = "Please Select..."
            End If
        
        Application.EnableEvents = True
        End If
       
    End If

End Sub