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

 

[Peter_SSs]

I want it to trigger only when cell "I4" or "J8" changes.

The code has to at least trigger to determine if one of those cells has changed. If you change another cell, what is the code doing that you don't want it to do?

Another weird thing is that if the delete key is hit in cell J8, an error happens.

That did not happen for me.
What is the full error message?
What line of code is highlighted if you 'Debug' when the error happens?

 

[Jemini Jimi]

The code has to at least trigger to determine if one of those cells has changed. If you change another cell, what is the code doing that you don't want it to do?

I just found out the Data Refresh All was set to 1 minute, which I thought was triggering so that part is fixed.

What line of code is highlighted if you 'Debug' when the error happens?

Runtime error on this line Case Is = "":

 

[Alex Blakenburg]

As it stands the lines below will run "any time any cell is changed".
When did you want those lines to run ? Only when I4 is changed ?

    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

 

[Alex Blakenburg]

I can only generate the error you are getting by deleting multiple cells at once.
Try changing your select statement per the below:

Select Case Target.Value
Select Case Range("J8").Value

 

[Peter_SSs]

Thanks for the error details.
That error will occur if you change the value in J8 and at least one other cell at the same time. (eg select J6:J9 and press 'Delete', or paste data into multiple cells including J8, or enter data into multiple cells including J8 using Ctrl+Enter)
The problem is that Target.Value is meaningless when Target consists of more than one cell.

Also Range(Target.Address) is not a good way to write that since Target already is a range with the address of itself.
This is not an error but not good coding. You had better syntax in the first 'Intersect' line at the top of your code.

So, as well as answering Alex's questions, the first thing that I would try is changing the two lines just under 'THIS PART DOES NOT WORK to

If Not Application.Intersect(Range("J8"), Target) Is Nothing Then
    Select Case Range("J8").Value

Just one other point. The 'case' Case Is = "" is not needed at all. That line can be taken out altogether.
If J8 is not one of the other two longer texts then nothing will happen anyway.

 

[Jemini Jimi]

Excellent, it's working!

That error will occur if you change the value in J8 and at least one other cell at the same time. (eg select J6:J9 and press 'Delete')

As you said, J8 is part of merge cells, so I changed that to J8:P8.

Select Case Range("J8").Value

With both issues fixed, here is the correct code.

If Not Application.Intersect(Range("J8:P8"), Target) Is Nothing Then
Select Case Range("J8").Value

Thanks, Guys, so much for your help!

How do I mark "Solution" giving both of you credit?

 

[Jemini Jimi]

When did you want those lines to run ? Only when I4 is changed ?

This section needs to run before (Range("J8:P8") are there will be an error

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

 

[Peter_SSs]

Excellent, it's working!

Good news. Thanks for letting us know.

How do I mark "Solution" giving both of you credit?

You can't, you can only mark one. The mark is to help future readers looking for similar help, not to give 'credit' to helpers here.


BTW, this is another code line not needed as the active sheet is already activated or it wouldn't be the active sheet.
ActiveSheet.Activate

 

[Alex Blakenburg]

This section needs to run before (Range("J8:P8") are there will be an error

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)

'THIS PART DOES NOT
    If Not Application.Intersect(Range("J8"), Range(Target.Address)) 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

Oops and move the 2 lines at the end before the last End If

    ActiveSheet.Protect
    Application.ScreenUpdating = True
End If