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