Please help me to extend the Code address-CodePudding

Please help me to extend the code address below, It works only with A1 and B1 cells, I need it for Column B and Column G.

In below Code: Target. Address <> "$A$1" >> Here I require it for column B and in G column i have my data validation input.

I bit confused with the Offset configuration in case of Column B and Column G. Thanks in advance

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Address <> "$A$1" Then Exit Sub
    If UCase(Target) = "" Then Target.Offset(, 1) = ""
End Sub

CodePudding user response：

We often use the Intersect function for this purpose:

 If Not Intersect(Target, Columns("B:B")) Is Nothing Or Not Intersect(Target, Columns("G:G")) Is Nothing Then Exit Sub

If you replace your first If Target.... statement with this, any changes made under columns B or G will not be treated any further (the sub will exit).

CodePudding user response：

To check whether a change has been made in Column B . . .

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

In case a user selects more than one cell in making a change . . .

Set Target = Target.Cells(1)

To prevent the change event from executing a second time . . .

Application.EnableEvents = False

Here's the complete code . . .

Private Sub Worksheet_Change(ByVal Target As Range)

    If Intersect(Target, Columns("B")) Is Nothing Then Exit Sub
    
    Set Target = Target.Cells(1)
    
    Application.EnableEvents = False
    If Target = "" Then
        Target.Offset(, 5) = ""
    End If
    Application.EnableEvents = True
    
End Sub