Worksheet change event to triger only if a specific cell value changes

[hrayani]

Hi friends,

I am using the below code to hide unhide rows based

Private Sub Worksheet_Change(ByVal Target As Range)

    
   ActiveSheet.Unprotect
    
    ActiveWorkbook.UpdateLink Name:= _
        "\\192.168.0.100\itex Share\ITEX\DATA ENTRY.xlsm", Type:=xlExcelLinks
      
    Dim xRg As Range
    Application.ScreenUpdating = False
        For Each xRg In Range("A23:A52,A61:A85")
            If xRg.Value = True Then
                xRg.EntireRow.Hidden = False
         
            Else
                xRg.EntireRow.Hidden = True
               
            End If
        Next xRg
        
       
       
                                
    ActiveSheet.Protect DrawingObjects:=False, Contents:=True, Scenarios:= _
    False, AllowFormattingCells:=True, AllowFormattingColumns:=True, _
    AllowFormattingRows:=True




End Sub

The problem is the code triggers every time a change is made to the sheet.
Can we just set the code to trigger only when a specific cell is changed.
In my case its cell E17


Regards,

Humayun

 

[Peter_SSs]

You can't stop the code triggering when any change is made, but you can make it so that the actions in your code are only processed if E17 is changed.

Private Sub Worksheet_Change(ByVal Target As Range)

  If Not Intersect(Target, Range("E17")) Is Nothing Then
  
    'rest of your code here

  End If

End Sub

 

[hrayani]

Thanks Peter,

Works Perfect....

Though I don't need it right now but would like to ask for my knowledge that how to add some more cells besides E17..
Like if i want the code to trigger if either E17 or J1 changes....

 

[Peter_SSs]

Try altering the "If... " line

If Not Intersect(Target, Range("E17, J30, P40")) Is Nothing Then

 

[hrayani]

I tried myself to add a second cell

If Not Intersect(Target, Range("E17:E17,J1:J1")) Is Nothing Then

Works Fine

 

[Peter_SSs]

I tried myself to add a second cell

If Not Intersect(Target, Range("E17:E17,J1:J1")) Is Nothing Then

Works Fine

Or just

If Not Intersect(Target, Range("E17, J1")) Is Nothing Then

 

[hrayani]

Thanks Peter

 

[Peter_SSs]

Thanks Peter

You are welcome.