Array Difference IF/OR VBA Script

Question

Have script that stores an array and if it changes it finds the difference. Now I need it to take that difference and check against if the adjacent cell is "John or Mary" and times the array value difference by: (If "John" = Array Value Diff. * PO#1) OR (If "Mary" = Array Value Diff. * PO#2). This is the final value I need written to Sheet 1 which right now is just the Array Value Diff. value.

Code in Module 1

Public myArr()
Public Sub PopulateMyArr()
    myArr = Sheet4.Range("I6:I500").Value
End Sub

Code in This Workbook

Private Sub Workbook_Open()
    PopulateMyArr
End Sub

Code in Sheet4

Private Sub ToggleButton1_Click()

End Sub

Private Sub Worksheet_Calculate()

Dim keyCells As Range
On Error GoTo safeexit
Application.EnableEvents = False

Set keyCells = Me.Range("I6:I500")

If Worksheets("BA_Size").ToggleButton1.Value = True Then
Dim i As Integer
For i = 1 To UBound(myArr)
If keyCells(i, 1).Value <> myArr(i, 1) Then
   
nextrow = Sheet1.Cells(Sheet1.Rows.Count, "A").End(xlUp).Row + 1
Sheet1.Cells(nextrow, "A").Value = (keyCells(i, 1).Value - myArr(i, 1))
End If
Next i
End If
safeexit:
PopulateMyArr
Application.EnableEvents = True
End Sub

Answer 1

Untested:

Private Sub Worksheet_Calculate()

    Dim keyCells As Range, i As Long, diff, cKey As Range
    
    'exit if togglebutton not on
    If Not Worksheets("BA_Size").ToggleButton1.Value Then Exit Sub
    
    On Error GoTo safeexit
    Application.EnableEvents = False
    
    Set keyCells = Me.Range("I6:I500")
    nextrow = Sheet1.Cells(Sheet1.Rows.Count, "A").End(xlUp).Row + 1
    
    For i = 1 To UBound(myArr)
        Set cKey = keyCells(i, 1)
        If cKey.Value <> myArr(i, 1) Then
            diff = (cKey.Value - myArr(i, 1))
            'check value in Col K
            Select Case cKey.EntireRow.Columns("K").Value
                Case "John": diff = diff * cKey.EntireRow.Columns("N").Value
                Case "Mary": diff = diff * cKey.EntireRow.Columns("O").Value
                Case Else: diff = 0
            End Select
            Sheet1.Cells(nextrow, "A").Value = diff
            nextrow = nextrow + 1
        End If
    Next i
      
safeexit:
    PopulateMyArr
    Application.EnableEvents = True
End Sub