Private Sub Worksheet_Change(ByVal Target As Range)
'Declare variables to hold current Starting Inventory and Inventory Removal values
Dim cD As Long, cE As Long, cF As Long, Ind As Long, sixP As Long
'Disable Events to prevent unexpected events from triggering code
Application.EnableEvents = False
'Check to see if values changed within range of H7:I18
If Not Intersect(Target, Range("H7:I18")) Is Nothing And Target.Count = 1 And Target.Value <> 0 Then
'Store current Starting Inventory values
cD = Range("D" & Target.Row).Value 'Column D value (cases)
cE = Range("E" & Target.Row).Value 'Column E value (6 packs)
cF = Range("F" & Target.Row).Value 'Column F value (individuals)
'Perform cascading calculation based on which column was updated (6-pack(H) or Individual(I))
Select Case Target.Column
'Column H calculation (6-pack)
Case 8 'Column H
'Store Inventory Removal value
sixP = Range("H" & Target.Row).Value
'Do the thing!
cE = cE - sixP
If cE < 0 And cD >= WorksheetFunction.RoundUp(Abs((cE * 6) / 24), 0) Then
cD = cD - WorksheetFunction.RoundUp(Abs((cE * 6) / 24), 0)
cE = cE + WorksheetFunction.RoundUp(Abs((cE * 6) / 24), 0) * 4
Else
cE = cE + (cD * 4)
cD = cD - cD
If cE < 0 And cF >= Abs(cE * 6) Then
cF = cF - Abs(cE * 6)
cE = cE + Abs(cE)
End If
End If
'Column I calculation (Individual)
Case 9 'Column I
'Store Inventory Removal value
Ind = Range("I" & Target.Row).Value
'Do the thing!
cF = cF - Ind
If cF < 0 And cE >= WorksheetFunction.RoundUp(Abs(cF / 6), 0) Then
cE = cE - WorksheetFunction.RoundUp(Abs(cF / 6), 0)
cF = cF + WorksheetFunction.RoundUp(Abs(cF / 6), 0) * 6
ElseIf cE < WorksheetFunction.RoundUp(Abs(cF / 6), 0) And (cD * 24) >= Abs(cF) Then
cD = cD - WorksheetFunction.RoundUp(Abs(cF / 24), 0)
cE = cE + (WorksheetFunction.RoundUp(Abs(cF / 24), 0) * 24) / 6
cE = cE - WorksheetFunction.RoundUp(Abs(cF / 6), 0)
cF = cF + WorksheetFunction.RoundUp(Abs(cF / 6), 0) * 6
Else
cF = cF + (cE * 6) + (cD * 24)
cE = cE - cE
cD = cD - cD
End If
End Select
'Update Starting Inventory values after calculations
Range("D" & Target.Row).Value = cD
Range("E" & Target.Row).Value = cE
Range("F" & Target.Row).Value = cF
End If
'Enable events so the code can work again on next update
Application.EnableEvents = True
End Sub