VBA not working as expected

[Russk68]

Hi All,
The code below in blue text is not working. I am expecting that when I make a change anywhere in column N that formulas in AW3:AW100 and BE3:BBE1000 will calculate.


Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(R, Target) Is Nothing Then

If Target.Count > 1 Then Exit Sub
If Target.Column <> 7 Then Exit Sub
If Target <> "" And Target.Offset(0, -6) = "" Then
Target.Offset(0, -6) = "Location Needed"
ElseIf Target = "" Then
Target.Offset(0, -6).ClearContents
End If

If Not Intersect(Target, Range("N:N")) Is Nothing Then
Range("AW3:AW1000,BE3:BE1000").Calculate
End If

If Not Intersect(Target, Range("p,s:s,v:v,y:y")) Is Nothing Then
Range("AS1:AS1000,AT1:AT1000,AU1:AU1000,AV1:AV1000").Calculate
End If

If Not Intersect(Target, Range("h:h,ao:ao")) Is Nothing Then
Range("c1:c1000,AR1:AR1000,Be1:Bh1000").Calculate
End If

If Not Intersect(Target, Range("a:a")) Is Nothing Then
Range("d1:d1000").Calculate 'Cable color
End If

For Each Cll In Range("AO3:AO50,BP2")
For Each TCll In Target
If Cll.Address = TCll.Address Then
ThisWorkbook.Sheets("Multi Build").Calculate
ThisWorkbook.Sheets("Distro").Calculate
Exit Sub
End If
Next TCll
Next Cll
End If
End Sub

 

[Russk68]

Not sure if it's the same as PC but try these steps:

In the VBE click on the margin to the left of

If Not Intersect(R, Target) Is Nothing Then

it should be highlighted and a dot placed in the margin:

View attachment 716
When you make a change on the worksheet the procedure will halt at that line anb be highlighted in yellow:
View attachment 717

Press F8 to step through the code and see where it errors/exits.

PS ignore the Dim r, that was added by me!

I See!
Thanks Paul