Error with two Workbook_SheetChange events

**nickmax1** · 08-24-2012, 07:38 AM

I understand you cant have two Workbook_SheetChange events in your YourWorkbook - but i need the macros to run throughout each sheet.
Here are my two.....can they be merged (they do very different things)?

Private Sub Workbook_SheetChange(ByVal sh As Object, _
ByVal Target As Excel.Range)
If Intersect(Target, sh.Range("C17:C190")) 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
    End With
    .SetRange sh.Range("A17:AJ190")
    .Header = xlGuess
    .MatchCase = False
    .Orientation = xlTopToBottom
    .SortMethod = xlPinYin
    .Apply
    Range("AJ17:AL17").Select
    Selection.AutoFill Destination:=Range("AJ17:AL190"), Type:=xlFillDefault
    Range("AJ17:AL190").Select
    ActiveWindow.ScrollColumn = 17
    ActiveWindow.ScrollColumn = 16
    ActiveWindow.ScrollColumn = 15
    ActiveWindow.ScrollColumn = 14
    ActiveWindow.ScrollColumn = 13
    ActiveWindow.ScrollColumn = 12
    ActiveWindow.ScrollColumn = 11
    ActiveWindow.ScrollColumn = 10
    ActiveWindow.ScrollColumn = 9
    ActiveWindow.ScrollColumn = 8
    ActiveWindow.ScrollColumn = 6
    ActiveWindow.ScrollColumn = 5
    ActiveWindow.ScrollColumn = 4
    ActiveWindow.ScrollColumn = 3
    ActiveWindow.ScrollColumn = 2
    ActiveWindow.ScrollColumn = 1
    Range("A17").Select
End With
Application.EnableEvents = True

End Sub

Private Sub Workbook_SheetChange(ByVal Target As Excel.Range)

If Target.Count > 1 Then Exit Sub

If Not Intersect(Target, Range("I18:I190")) Is Nothing Then
    With Target
        If UCase(Cells(.Row, "I")) = "NTU" Then
            Cells(.Row, "J") = "Red"
        End If
        If Application.Proper(Cells(.Row, "I")) = "Declined" Then
            Cells(.Row, "J") = "Red"
        End If
        If Application.Proper(Cells(.Row, "I")) = "Bound" Then
            Cells(.Row, "J") = "Green"
            End If
        If Application.Proper(Cells(.Row, "I")) = "Extended" Then
            Cells(.Row, "J") = "Green"
            End If
        If Application.Proper(Cells(.Row, "I")) = "Non-Renewed" Then
            Cells(.Row, "J") = "Red"
        End If
        If Application.Proper(Cells(.Row, "I")) = "Modelling" Then
            Cells(.Row, "J") = "Amber"
         End If
         If Application.Proper(Cells(.Row, "I")) = "Quoted" Then
            Cells(.Row, "J") = "Amber"
        End If
         If UCase(Cells(.Row, "I")) = "WIP" Then
            Cells(.Row, "J") = "Amber"
        End If
        
    End With
End If

End Sub

**JapanDave** · 08-24-2012, 07:50 AM

Why not use the event macro to call the macro from a standard module?

**nickmax1** · 08-24-2012, 08:02 AM

please explain?! i am a noob.

**JapanDave** · 08-24-2012, 08:10 AM

Post a sample workbook, It will be easier to modify the code than explain it. Then you can play with the code to see how it works.

**nickmax1** · 08-24-2012, 08:14 AM

Activity test.xlsm

See new code in YOURWORKBOOK, this wont run as I have a change event already...

**JapanDave** · 08-24-2012, 08:26 AM

I don't understand what you are trying to do?

**nickmax1** · 08-24-2012, 08:46 AM

I want the event to fire off my current event macro that effectively sorts by column c. My second event that is clashing simply changes one cell based on the value of another