How to Update Stock quantity if I sold items?

Hello Saad3000,

The attached workbook contains the following macros. The main macro adjusts the stock level based on the current stock less what has been sold. The other macro is in the Worksheet_Change() event which checks the entry and then calls the main macro.

Stock Level (main macro)

Function StockLevel(Item_Name As String, Qty_Sold As Double) As Variant
 
  Dim OnHand As Double
  Dim Result As Range
  Dim Rng As Range
  
    Set Rng = Worksheets("Sheet1").Range("Batteries")
    
    If Item_Name = "" Or Qty_Sold < 0 Then
       StockLevel = ""
       Exit Function
    End If
    
      Set Result = Rng.Find(What:=Item_Name, After:=Rng.Cells(1, 1))
        If Result Is Nothing Then
           StockLevel = ""
           Exit Function
        End If
        
      OnHand = Result.Offset(0, 1).Value - Qty_Sold
        If OnHand <= 0 Then
           Result.Offset(0, 1) = 0
           StockLevel = 0
        Else
           Result.Offset(0, 1).Value = OnHand
           StockLevel = OnHand
        End If
                     
End Function

Worksheet_Change() Event Macro

Private Sub Worksheet_Change(ByVal Target As Range)

  If Not Intersect(Target, Range("B2:B6")) Is Nothing Then
     Call StockLevel(Target.Offset(0, -1).Value, Target.Value)
  End If
  
End Sub

Sincerely,
Leith Ross