Because you have not limited the procedure to any particular worksheet or Range, it will run completely thru all the code on every change on every worksheet. In addition to that, it will cause a cascade of changes (see italics above) everytime it assigns a formula to a cell, even if the formula is the same as it was.
Stack Overflow indeed.
First, insure that this sub only runs when the sheet that needs it is changed:
Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)If Not Sh.Name = "Sheet1" Then Exit Sub 'Edit "Sheet1" to suit.
Blah, Blah, blah
End Sub
Second, insure that it only runs when the Ranges that need it are changed.
Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
If Not Sh.Name = "Sheet1" Then Exit Sub 'Edit "Sheet1" to suit.
Dim RangeToWatch As Range
Set RangeToWatch = Sh.Range("D4", "D18") 'Edit to Suit
If Intersect(Target, RangeToWatch) Is Nothing Then Exit Sub
Blah, Blah, blah
End Sub
Third, insure that only one instance of ther sub can run at a time
Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
If Not Sh.Name = "Sheet1" Then Exit Sub
Static MeAlreadyRunning As Boolean
If MeAlreadyRunning Then Exit Sub
MeAlreadyRunning = True
Dim RangeToWatch As Range
Set RangeToWatch = Sh.Range("D4", "D18") 'Edit to Suit
If Intersect(Target, RangeToWatch) Is Nothing Then Exit Sub
Blah, Blah, blah
MeAlreadyRunning = False
End Sub
Finally, insure that Excel ignores changes made by the sub when it does run all the way thru.
Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
Dim RangeToWatch As Range
Static MeAlreadyRunning As Boolean
If MeAlreadyRunning Then Exit Sub
MeAlreadyRunning = True
If Not Sh.Name = "Sheet1" Then Exit Sub 'Edit "Sheet1" to suit
Set RangeToWatch = Sh.Range("D4", "D18") 'Edit to Suit
If Intersect(Target, RangeToWatch) Is Nothing Then Exit Sub
With Application
.EnableEvents = False
.Calculation = xlCalculationManual
.ScreenUpdating = False
End With
Blah, Blah, blah
'And turn Things back on in particular order
Sh.Range("F28").Calculate
With Application
.Calculation = xlCalculationAutomatic
.ScreenUpdating = True
.EnableEvents = False
End With
MeAlreadyRunning = False
End Sub