Solved: worksheet value change affecting 2 cells

[khalid79m]

	
Private Sub Worksheet_Change(ByVal Target As Range)

Application.ScreenUpdating = False
'Page 3
 
If ActiveCell.Address = ThisWorkbook.Sheets("Intervention_3.0").Range("C95").Address Then
    Run "Int3_Update_Page3"
    Exit Sub
End If
 
If ActiveCell.Address = ThisWorkbook.Sheets("Intervention_3.0").Range
("H95").Address Then
    Range("M95:N95").ClearContents
    Exit Sub
End If
 
If ActiveCell.Address = ThisWorkbook.Sheets("Intervention_3.0").Range("M95").Address Then
    Run "Int3_Update_Page3"
    Exit Sub
End If
Application.ScreenUpdating = True

End Sub

this above runs the Int3 Update Page3 if C95 or M95 changes, if H95 changes then M95:N95's contents are clearer. Only one problem on h95 changeing the contents of m95:n95 are cleared but since m95 has changed it runs the update. Can I stop it from doing that

[lucas]

Khalid, I can't get your first IF statement to work.

Can you attach an example workbook?

[macropod]

Hi khalid79m,

Try:

Private Sub Worksheet_Change(ByVal Target As Range)
'Page 3
With Application
  .ScreenUpdating = False
  .EnableEvents = False
  With ThisWorkbook.Sheets("Intervention_3.0")
    If ActiveCell.Address = "C95" Or ActiveCell.Address = "M95" Then
      Run "Int3_Update_Page3"
    ElseIf ActiveCell.Address = "H95" Then
      Range("M95:N95").ClearContents
    End If
  End With
  .EnableEvents = True
  .ScreenUpdating = True
End With
End Sub

[khalid79m]

i cant post a workbook as it is confidential info, ill see i can create a dummy sheet.

[Bob Phillips]

[vba]

Private Sub Worksheet_Change(ByVal Target As Range)
Dim OtherSh As Worksheet

On Error GoTo ws_exit

Application.EnableEvents = False
Application.ScreenUpdating = False
'Page 3

Set OtherSh = ThisWorkbook.Sheets("Intervention_3.0")

If Target.Address = OtherSh.Range("C95").Address Then
Run "Int3_Update_Page3"
ElseIf Target.Address = OtherSh.Range("H95").Address Then
Me.Range("M95:N95").ClearContents
ElseIf ActiveCell.Address = OtherSh.Range("M95").Address Then
Run "Int3_Update_Page3"
End If

ws_exit:
Application.EnableEvents = True
Application.ScreenUpdating = True

End Sub
[/vba]

[khalid79m]

again xld you did it..