Change event removes contents outside of target range

[rex759]

Hello,
Here’s snippet of a Private Sub Worksheet_Change(ByVal Target as range)

If Not Intersect(Target, Range("B4:B27")) Is Nothing And Cells(Target.Row, "B") <> "Surveys" Or Cells(Target.Row, "B") <> "Reviews" And Cells(Target.Row, "D").Value > 0 Then
        Cells(Target.Row, "D").Value = ""
End If

The problem I’m having is its removing header information in range D1, D2 & D3. Is there a way to have the code change rows 4:27 only?

Any help is appreciated

 

[Joe4]

I think the issue is with your mix of AND and ORs, and not using any parentheses to clarify what needs to be checked and when.
Just guessing, but tty and see if this is what you are after (note I broke it up to three lines to show the three different levels we are checking):

If (Not Intersect(Target, Range("B4:B27")) Is Nothing) _
    And (Cells(Target.Row, "B") <> "Surveys" Or Cells(Target.Row, "B") <> "Reviews") _
    And (Cells(Target.Row, "D").Value > 0) Then
        Cells(Target.Row, "D").Value = ""
End If

 

[rex759]

Thank you! Still digesting the parentheses placement but it works perfectly.

 

[Joe4]

You are welcome!

Think of the parentheses as "sets of conditions". So it is really:
Set1 AND Set2 AND Set3
Note that the OR statement is within Set2. Without the parentheses, the mixture of AND or OR won't work the way you want.