+ Reply to Thread
Results 1 to 3 of 3

Event macro to skip worksheet

Hybrid View

  1. #1
    Valued Forum Contributor
    Join Date
    08-10-2012
    Location
    London, England
    MS-Off Ver
    Excel 2010
    Posts
    721

    Event macro to skip worksheet

    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

  2. #2
    Forum Expert tigeravatar's Avatar
    Join Date
    03-25-2011
    Location
    Colorado, USA
    MS-Off Ver
    Excel 2003 - 2013
    Posts
    5,361

    Re: Event macro to skip worksheet

    At top of code with your other Exit Sub condition:
    If Sh.Name = "Summary" Or Sh.Name = "Graphs" Then Exit Sub
    Hope that helps,
    ~tigeravatar

    Forum Rules: How to use code tags, mark a thread solved, and keep yourself out of trouble

  3. #3
    Valued Forum Contributor
    Join Date
    08-10-2012
    Location
    London, England
    MS-Off Ver
    Excel 2010
    Posts
    721

    Re: Event macro to skip worksheet

    perfect!! so simple thanks very much......marking as solved.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1