Excel Shuts Down After Running VBA Code

[PatHay]

Hi, for a few years now I have used a lot of VBA to achieve various things in Excel, but it is true to say that my in depth understanding of the code is minimal. Mostly, I have managed to resolve various issues along the way with Googles help and also from this forum which has been totally invaluable.

I have hit an issue now re the code below, which puts the value True into the range of cell(s) required, but then Excel crashes/quits and just shuts itself down. What am I missing in the code please to stop this from happening?

================================================
Private Sub Worksheet_Change(ByVal Target As Range)


Dim FndRng As Range
Dim cell As Range

Set FndRng = Range("BA1:BA20")

For Each cell In FndRng
If cell.Value = "True" Then

cell.Offset(0, 3) = cell.Value

End If

Next cell

End Sub
=============================================

Any help here will be very much appreciated. Thank you.

 

[PatHay]

That is interesting re that "Worksheet_Calculate" event and how it works.

The formula in BA1 is: =IF(AND(I10>0,I10<1.71),"TRUE","WatchingRedFlags")
Once that cell is TRUE, then that's when I need it copied to that offset cell range.

Is a good learning curve re this one and is definitely helping me to understand more re how VBA works under the hood. Appreciate you helping out here with these simple explanations that are easy to follow re how VBA all bolts together .

 

[Joe4]

So, is there some sort of pattern to the formulas?
i.e. it appears that BA1 s looking at I10

So is BA2 looking at I11, and BA3 looking at I12, etc?
Or is there some other discernible pattern?

 

[PatHay]

Yes that is correct re the change of formula from cell BA1 onwards. Sorry, I should have mentioned that. Thanks for the swift response.

 

[Joe4]

OK then, if manual changes to cells I10:I29 affect the formulas in cells BA1:BA20, then we can amend our code to watch for changes to I10:I29 instead, i.e.

Private Sub Worksheet_Change(ByVal Target As Range)

    Dim FndRng As Range
    Dim cell As Range

'   See if any cells updated in range I10:I29 (if not, exit sub)
    Set FndRng = Intersect(Range("I10:I29"), Target)
    If FndRng Is Nothing Then Exit Sub

'   Loop through cells just changed in range BA1:BA20
    For Each cell In FndRng
'       See if value in formula in column BA changes to "True" (I10 matches to BA20)
        If cell.Offset(-9, 44).Value = "True" Then
            Application.EnableEvents = False
            cell.Offset(-9, 47) = cell.Offset(-9, 44).Value
            Application.EnableEvents = True
        End If
    Next cell

End Sub