Using worksheet_change to alter a different worksheet

[ems97]

I am trying to use a Worksheet_Change event so that when columns E or F are changed on Sheet1, a range on Sheet7 will be sorted. Whenever I make a change in the desired location, an error message comes up that says "subscript out of range." How do I make it so that this error does not come up (and also, how do I make it so that only changes to column E or F will trigger the macro - not changes to the other columns)?

Private Sub Worksheet_Change(ByVal Target As Range)
Worksheets("Sheet7").Range("A1:F18").Sort Key1:=Range("F2"), Order1:=xlDescending, Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal
End Sub

 

[VoG]

Try

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column < 5 Or Target.Column > 6 Then Exit Sub
With Sheet7
    .Range("A1:F18").Sort Key1:=.Range("F2"), Order1:=xlDescending, Header:=xlGuess, _
    OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
End With
End Sub