Runtime error

[Jammydan]

Please can anybody help. I have the following code ehich I get a runtime error. If I remove the protect sheet code it runs fine??

Private Sub Worksheet_Activate()
ActiveSheet.Unprotect Password:="harryemma1"
Range("H2") = Range("H2") + 1
'**** SORT BY VALUE DATE *****
    Selection.Sort Key1:=Range("M19"), Order1:=xlAscending, Header:=xlGuess
    Range("C18").End(xlDown).Offset(1, 0).Select
    ActiveSheet.Protect Password:="harryemma1"
End Sub
Private Sub Worksheet_Change(ByVal Target As Range)
    Dim IntMagic As Range
    Dim DateMagic As Range
    Set IntMagic = Range("W19:W50")
    Set DateMagic = Range("N19:N50")
    
ActiveSheet.Unprotect Password:="harryemma1"
For Each Cell In IntMagic
        If Cell.Value = "No" Then ' Orange
        Range("P" + CStr(Cell.Row) + ":X" + CStr(Cell.Row)).Interior.ColorIndex = 27
        ElseIf Cell.Value = "Yes" Then ' Ivory
        Range("P" + CStr(Cell.Row) + ":X" + CStr(Cell.Row)).Interior.ColorIndex = 19
        Else ' Remove all color
        Range("P" + CStr(Cell.Row) + ":X" + CStr(Cell.Row)).Interior.ColorIndex = 0
    End If
Next
For Each Cell In DateMagic
        If Cell.Value = "TODAY" Then ' Green
        Range("B" + CStr(Cell.Row) + ":O" + CStr(Cell.Row)).Interior.ColorIndex = 43
        ElseIf Cell.Value = "OVERDUE" Then ' Red
        Range("B" + CStr(Cell.Row) + ":O" + CStr(Cell.Row)).Interior.ColorIndex = 3
        ElseIf Cell.Value = "TOMORROW" Then ' Gold
        Range("B" + CStr(Cell.Row) + ":O" + CStr(Cell.Row)).Interior.ColorIndex = 44
        ElseIf Cell.Value = "SOON" Then ' Yellow
        Range("B" + CStr(Cell.Row) + ":O" + CStr(Cell.Row)).Interior.ColorIndex = 36
        ElseIf Cell.Value = ">WEEK" Then ' Ivory
        Range("B" + CStr(Cell.Row) + ":O" + CStr(Cell.Row)).Interior.ColorIndex = 19
        Else ' Ivory
        Range("B" + CStr(Cell.Row) + ":O" + CStr(Cell.Row)).Interior.ColorIndex = 0
    End If
Next
ActiveSheet.Protect Password:="harryemma1"
End Sub

 

[Jerry Sullivan]

Hi Jammydan,

Two things I notice on a quick review:

1. In the statement below "Selection" is not previously defined in your code.

 Selection.Sort Key1:=Range("M19"), Order1:=xlAscending, Header:=xlGuess

At the beginning of a worksheet_activate procedure, this will cause VBA to use the last Selected cells, the previous time the worksheet was active, which doesn't give you much control. Replace "Selection", with an explicit range reference.

2. To prevent an endless loop when using Worksheet_Change events, temporarily set the Application.EnableEvents to False...

Application.EnableEvents = False
'.....your code that might trigger other events
Application.EnableEvents = True

You might have another error or errors generating the Run-time error, but fixing these two points will help reduce the potential causes.

 

[Jammydan]

Thanks Jerry