MERGE TWO Worksheet_Change codes

[mervesy]

Hi,
I am trying to merge two VBA codes;

Code1:

Private Sub Worksheet_Change(ByVal Target As Range)
' If Target cell is B6 then...
If Target.Column = 6 Then
' Clear contents of E6
Target.Offset(0, 1).ClearContents
' Clear contents of H6
Target.Offset(0, 2).ClearContents
Target.Offset(0, 3).ClearContents
Target.Offset(0, 4).ClearContents
Target.Offset(0, 5).ClearContents
Target.Offset(0, 6).ClearContents
Target.Offset(0, 7).ClearContents
Target.Offset(0, 8).ClearContents
Target.Offset(0, 9).ClearContents
Target.Offset(0, 10).ClearContents
Target.Offset(0, 11).ClearContents
End If
End Sub



Code2:

Private Sub Worksheet_Change(ByVal Target As Range)
Set xxx = Intersect(Target, Range("E2:F32,H2:H32")) 'it's a range that is the intersection of the cells you have tried to change (Target) and the cells that have DV that you want to protect.
If Not xxx Is Nothing Then
'Does the validation range still have validation?
If HasValidation(xxx) Then
Exit Sub
Else
Application.EnableEvents = False
Application.Undo
Application.EnableEvents = True
MsgBox "Your last operation was cancelled. It would have deleted data validation rules.", vbCritical
End If
End If
End Sub


Private Function HasValidation(r) As Boolean
HasValidation = True
'Returns True if every cell in Range r uses Data Validation
On Error Resume Next
For Each cll In r.Cells
x = cll.Validation.Type
If Err.Number <> 0 Then
HasValidation = False
Exit For
End If
Next cll
End Function


It looks like i have to change something but i couldn't find. Is there anyone to explain?

Thanks!

 

[My Aswer Is This]

First line of your code does not do what your comment says:
You show:

' If Target cell is B6 then...
If Target.Column = 6 Then

It should be:

If you want to clear B7 to M7

Private Sub Worksheet_Change(ByVal Target As Range)
'Modified  6/28/2019  11:37:48 AM  EDT
If Target.Address = Range("B6").Address Then
Target.Offset(, 1).Resize(, 11).ClearContents
End If
End Sub

As far as the rest of your code please explain in words what your attempting to do. I cannot understand from looking at your code what your wanting to accomplish.

​

 

[mervesy]

First line of your code does not do what your comment says:
You show:

' If Target cell is B6 then...
If Target.Column = 6 Then

It should be:

If you want to clear B7 to M7

Private Sub Worksheet_Change(ByVal Target As Range)
'Modified  6/28/2019  11:37:48 AM  EDT
If Target.Address = Range("B6").Address Then
Target.Offset(, 1).Resize(, 11).ClearContents
End If
End Sub

As far as the rest of your code please explain in words what your attempting to do. I cannot understand from looking at your code what your wanting to accomplish.

​

Hi;
I have a big data and I want to clear related row when something has changed in column B. I am not just trying to clear B7 to M7. That's why, first code works for me. But you're right; the comment is not exactly correct Both codes work fine seperately but i couldnt merge them.

 

[mervesy]

I wrote it wrong; i want to clear related row when something has changed in column 6(F).