I am fairly new to VBA, please forgive me. This code is probably bloated and can be cleaned up (not sure how), but when I have multiple cells in the target filled with a value that fires the worksheet change event, I can delete them one at with no issue. If I delete more than one cell at a time, I end up with the type 13 mismatch error. The code is supposed to check for a value change in column c, if it is one of two values it is supposed to insert "N/A" into several other cells on that same row (some non-contiguous). If it is not that value, then it is supposed to remain blank. It is paired with a code to check that same column for a third value, if it is the third value, it is supposed to return "N/A" to a single cell in that row. The error is highlighting "If Target.Value = "Wind Sites" Or Target.Value = "Springbok 3" Then" as the issue here.
I am fairly new to VBA, please forgive me. This code is probably bloated and can be cleaned up (not sure how), but when I have multiple cells in the target filled with a value that fires the worksheet change event, I can delete them one at with no issue. If I delete more than one cell at a time, I end up with the type 13 mismatch error. The code is supposed to check for a value change in column c, if it is one of two values it is supposed to insert "N/A" into several other cells on that same row (some non-contiguous). If it is not that value, then it is supposed to remain blank. It is paired with a code to check that same column for a third value, if it is the third value, it is supposed to return "N/A" to a single cell in that row. The error is highlighting "If Target.Value = "Wind Sites" Or Target.Value = "Springbok 3" Then" as the issue here.
Private Sub Worksheet_Change(ByVal Target As Range)
Dim WS As Worksheet
Set WS = Sheet4
If Not Intersect(Target, Range("C:C")) Is Nothing Then
Application.EnableEvents = False
If Target.Value = "Wind Sites" Or Target.Value = "Springbok 3" Then
Target.Offset(0, 2) = "N/A"
Target.Offset(0, 3) = "N/A"
Target.Offset(0, 4) = "N/A"
Target.Offset(0, 5) = "N/A"
Target.Offset(0, 7) = "N/A"
Target.Offset(0, 9) = "N/A"
Target.Offset(0, 10) = "N/A"
Else
Target.Offset(0, 2).ClearContents
Target.Offset(0, 3).ClearContents
Target.Offset(0, 4).ClearContents
Target.Offset(0, 5).ClearContents
Target.Offset(0, 7).ClearContents
Target.Offset(0, 9).ClearContents
Target.Offset(0, 10).ClearContents
End If
Application.EnableEvents = True
End If
On Error Resume Next
If Target.Count > 1 Then Exit Sub
If Application.Intersect(Target, Range("C:C")) Is Nothing Then Exit Sub
If Target.Value = "Galloway 1" Then
Target.Offset(0, 8) = ""
Else
If Target.Count > 1 Then Exit Sub
If Application.Intersect(Target, Range("C:C")) Is Nothing Then Exit Sub
If Target.Value <> "Galloway 1" Then
Target.Offset(0, 8) = "N/A"
If Target.Count > 1 Then Exit Sub
If Target.Count > 1 Then Exit Sub
If Application.Intersect(Target, Range("C:C")) Is Nothing Then Exit Sub
If Target.Value = "" Then
Target.Offset(0, 8).ClearContents
If Target.Count > 1 Then Exit Sub
End If
End If
End If
End Sub
Base on the logic of your code, Change event could be simiplified as below.
Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
With Target
If .CountLarge > 1 Then Exit Sub
If Not .Column = 3 Then Exit Sub
Application.EnableEvents = False
If .Value = "Wind Sites" Or .Value = "Springbok 3" Then
.Offset(0, 2).Resize(1, 4) = "N/A"
.Offset(0, 7).Resize(1, 4) = "N/A"
Else
.Offset(0, 2).Resize(1, 4).ClearContents
.Offset(0, 7).Resize(1, 4).ClearContents
End If
If .Value = "Galloway 1" Or .Value = "" Then
.Offset(0, 8).ClearContents
Else
.Offset(0, 8) = "N/A"
End If
Application.EnableEvents = True
End With
End Sub