I want my event macro to NOT fire for the sheets named "Summary" or "Graphs". How do i amend the below?
Private Sub Workbook_SheetChange(ByVal Sh As Object, _
ByVal Target As Excel.Range)
If Intersect(Target, Sh.Range("C17:C190")) Is Nothing And Intersect(Target, Sh.Range("I18:I190")) Is Nothing Then Exit Sub
Application.EnableEvents = False
With Sh.Sort
With .SortFields
.Clear
.Add Key:=Sh.Range("C17:C190"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
.Add Key:=Sh.Range("A17:A190"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
End With
.SetRange Sh.Range("A17:AJ190")
.Header = xlGuess
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
With Application
If Not Intersect(Target, Sh.Range("I18:I190")) Is Nothing Then
If UCase(Sh.Cells(Target.Row, 9)) = "NTU" Then
Sh.Cells(Target.Row, 10) = "Red"
ElseIf .Proper(Sh.Cells(Target.Row, 9)) = "Declined" Then
Sh.Cells(Target.Row, 10) = "Red"
ElseIf .Proper(Sh.Cells(Target.Row, 9)) = "Bound" Then
Sh.Cells(Target.Row, 10) = "Green"
ElseIf .Proper(Sh.Cells(Target.Row, 9)) = "Extended" Then
Sh.Cells(Target.Row, 10) = "Green"
ElseIf .Proper(Sh.Cells(Target.Row, 9)) = "Non-Renewed" Then
Sh.Cells(Target.Row, 10) = "Red"
ElseIf .Proper(Sh.Cells(Target.Row, 9)) = "Modelling" Or .Proper(Sh.Cells(Target.Row, "I")) _
= "Quoted" Or UCase(Sh.Cells(Target.Row, 9)) = "WIP" Then
Sh.Cells(Target.Row, 10) = "Amber"
ElseIf Sh.Cells(Target.Row, 9) = "" Then
Sh.Cells(Target.Row, 10).ClearContents
End If
End If
End With
Application.EnableEvents = True
End Sub
Bookmarks