Need help with "Private Sub Worksheet_Change(ByVal Target As Range)"

[Jemini Jimi]

I have merged two Private Sub Worksheet_Change(ByVal Target As Range) functions. It works but has one big flaw: It runs every time I change any cell.
I want it to trigger only when cell "I4" or "J8" changes. Another weird thing is that if the delete key is hit in cell J8, an error happens.

Private Sub Worksheet_Change(ByVal Target As Range)
'this part works fine
    If Not Intersect(Target, Me.Range("I4")) Is Nothing Then SaveAsFilenameInCell

    Application.ScreenUpdating = False
    ActiveSheet.Activate
    ActiveSheet.Unprotect
        Range("72:86").EntireRow.Hidden = False
        Rows("73:73").EntireRow.Hidden = True
        Rows("86:86").EntireRow.Hidden = True

'THIS PART DOES NOT
    If Not Application.Intersect(Range("J8"), Range(Target.Address)) Is Nothing Then
        Select Case Target.Value
    'this part needs to be if anything other than the other cases below then do nothing
    'if I hit the Delete key in cell J8 an error happens
    Case Is = "":

    Case Is = "Surface Preparation Technologies, LLC":
        Rows("72:72").EntireRow.Hidden = True
        Rows("73:73").EntireRow.Hidden = False
        'peek
        Rows("86:86").EntireRow.Hidden = True

    Case Is = "Peek Pavement Markings, LLC":
        Rows("85:85").EntireRow.Hidden = True
        Rows("86:86").EntireRow.Hidden = False
        'surface
        Rows("73:73").EntireRow.Hidden = True

    End Select
End If
    ActiveSheet.Protect
    Application.ScreenUpdating = True
End Sub

 

[Jemini Jimi]

Based on this you should move that code to after the If J8 intersect and before the Select Case did you make that change ?
(also remove the activate as per Peter's suggestion)

Ok, I moved it.
Thanks again, guys. You both were so helpful. I learned a lot!
Here is the fixed code.

Private Sub Worksheet_Change(ByVal Target As Range)
    If Not Intersect(Target, Me.Range("I4")) Is Nothing Then SaveAsFilenameInCell

    If Not Application.Intersect(Range("J8:P8"), Target) Is Nothing Then
    Application.ScreenUpdating = False
    ActiveSheet.Unprotect
    Range("72:86").EntireRow.Hidden = False
    Rows("73:73").EntireRow.Hidden = True
    Rows("86:86").EntireRow.Hidden = True
    Select Case Range("J8").Value
    Case Is = "":

    Case Is = "Surface Preparation Technologies, LLC":
        Rows("72:72").EntireRow.Hidden = True
        Rows("73:73").EntireRow.Hidden = False
        'peek
        Rows("86:86").EntireRow.Hidden = True

    Case Is = "Peek Pavement Markings, LLC":
        Rows("85:85").EntireRow.Hidden = True
        Rows("86:86").EntireRow.Hidden = False
        'surface
        Rows("73:73").EntireRow.Hidden = True

    End Select
    ActiveSheet.Protect
    Application.ScreenUpdating = True
 End If
End Sub

 

[Alex Blakenburg]

Appreciate the feedback. Glad we both could help.