Private Sub Worksheet_Change(ByVal Target As Range)
Dim salesRange As Range
Dim soldQuantity As Double
Dim currentQuantity As Double
Dim productRow As Range
Dim productName As String
Dim productCell As Range
' Define the range where you will input the sales quantity (F column)
Set salesRange = Me.Range("F2:F100") ' Change this range according to your needs
' Check if the changed cell is within the sales entry range
If Not Intersect(Target, salesRange) Is Nothing Then
' Loop through each cell that was changed
For Each cell In Target
' Check if the entered value is numeric and greater than 0
If IsNumeric(cell.Value) And cell.Value > 0 Then
' Get the corresponding product name from column A (assuming A is product name)
productName = cell.Offset(0, -5).Value ' Column A is 5 columns to the left of F
' Find the product in column A to update its quantity
Set productCell = Me.Range("A:A").Find(What:=productName, LookIn:=xlValues, LookAt:=xlWhole)
' If product is found, update the quantity in column B
If Not productCell Is Nothing Then
currentQuantity = productCell.Offset(0, 1).Value ' B column is next to A
soldQuantity = cell.Value + currentQuantity
productCell.Offset(0, 1).Value = soldQuantity ' Update quantity in B column
cell.Value = "" ' Clear the entry in the F column
Else
MsgBox "Product not found in the list."
End If
ElseIf Not IsNumeric(cell.Value) Then
MsgBox "Please enter a valid number for the sold quantity."
cell.Value = "" ' Clear the invalid entry
End If
Next cell
End If
End Sub
