Juriemagic
09-05-2016, 04:09 AM
Hi good people!,
I need to add another 40 ranges to this code, and I'm afraid it might slow Excel down. Could anyone please help me make this code shorter? The code is:
Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("C9:O9")) Is Nothing Then
With Sheets("Work Order 1")
.Range("P3").Value = 0
End With
End If
If Not Intersect(Target, Range("C12:O12")) Is Nothing Then
With Sheets("Work Order 2")
.Range("P3").Value = 0
End With
End If
If Not Intersect(Target, Range("C15:O15")) Is Nothing Then
With Sheets("Work Order 3")
.Range("P3").Value = 0
End With
End If
If Not Intersect(Target, Range("C18:O18")) Is Nothing Then
With Sheets("Work Order 4")
.Range("P3").Value = 0
End With
End If
If Not Intersect(Target, Range("C21:O21")) Is Nothing Then
With Sheets("Work Order 5")
.Range("P3").Value = 0
End With
End If
If Not Intersect(Target, Range("C24:O24")) Is Nothing Then
With Sheets("Work Order 6")
.Range("P3").Value = 0
End With
End If
If Not Intersect(Target, Range("C27:O27")) Is Nothing Then
With Sheets("Work Order 7")
.Range("P3").Value = 0
End With
End If
If Not Intersect(Target, Range("C30:O30")) Is Nothing Then
With Sheets("Work Order 8")
.Range("P3").Value = 0
End With
End If
If Not Intersect(Target, Range("C33:O33")) Is Nothing Then
With Sheets("Work Order 9")
.Range("P3").Value = 0
End With
End If
If Not Intersect(Target, Range("C36:O36")) Is Nothing Then
With Sheets("Work Order 10")
.Range("P3").Value = 0
End With
End If
If Not Intersect(Target, Range("C39:O39")) Is Nothing Then
With Sheets("Work Order 11")
.Range("P3").Value = 0
End With
End If
If Not Intersect(Target, Range("C42:O42")) Is Nothing Then
With Sheets("Work Order 12")
.Range("P3").Value = 0
End With
End If
If Not Intersect(Target, Range("C45:O45")) Is Nothing Then
With Sheets("Work Order 13")
.Range("P3").Value = 0
End With
End If
If Not Intersect(Target, Range("C48:O48")) Is Nothing Then
With Sheets("Work Order 14")
.Range("P3").Value = 0
End With
End If
End Sub
All and any help will be accepted with great humility and appreciation!..Thank you all..
I need to add another 40 ranges to this code, and I'm afraid it might slow Excel down. Could anyone please help me make this code shorter? The code is:
Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("C9:O9")) Is Nothing Then
With Sheets("Work Order 1")
.Range("P3").Value = 0
End With
End If
If Not Intersect(Target, Range("C12:O12")) Is Nothing Then
With Sheets("Work Order 2")
.Range("P3").Value = 0
End With
End If
If Not Intersect(Target, Range("C15:O15")) Is Nothing Then
With Sheets("Work Order 3")
.Range("P3").Value = 0
End With
End If
If Not Intersect(Target, Range("C18:O18")) Is Nothing Then
With Sheets("Work Order 4")
.Range("P3").Value = 0
End With
End If
If Not Intersect(Target, Range("C21:O21")) Is Nothing Then
With Sheets("Work Order 5")
.Range("P3").Value = 0
End With
End If
If Not Intersect(Target, Range("C24:O24")) Is Nothing Then
With Sheets("Work Order 6")
.Range("P3").Value = 0
End With
End If
If Not Intersect(Target, Range("C27:O27")) Is Nothing Then
With Sheets("Work Order 7")
.Range("P3").Value = 0
End With
End If
If Not Intersect(Target, Range("C30:O30")) Is Nothing Then
With Sheets("Work Order 8")
.Range("P3").Value = 0
End With
End If
If Not Intersect(Target, Range("C33:O33")) Is Nothing Then
With Sheets("Work Order 9")
.Range("P3").Value = 0
End With
End If
If Not Intersect(Target, Range("C36:O36")) Is Nothing Then
With Sheets("Work Order 10")
.Range("P3").Value = 0
End With
End If
If Not Intersect(Target, Range("C39:O39")) Is Nothing Then
With Sheets("Work Order 11")
.Range("P3").Value = 0
End With
End If
If Not Intersect(Target, Range("C42:O42")) Is Nothing Then
With Sheets("Work Order 12")
.Range("P3").Value = 0
End With
End If
If Not Intersect(Target, Range("C45:O45")) Is Nothing Then
With Sheets("Work Order 13")
.Range("P3").Value = 0
End With
End If
If Not Intersect(Target, Range("C48:O48")) Is Nothing Then
With Sheets("Work Order 14")
.Range("P3").Value = 0
End With
End If
End Sub
All and any help will be accepted with great humility and appreciation!..Thank you all..