Compare values in one sheet to alternating columns in another sheet

Question

I'm an absolute beginner to VBA with no coding background, and trying to make a sort of inventory application. I got stucked and tried so many code snippets found on internet to no avail.

I'm trying to compare stock values (on stock_sheet) to cell value entered on distribution_sheet ** Tr or En columns, and if dist value is greater tell user to enter values less or equal to stock values.

For example:

If user enters value in cells F5, H5, J5,...,BN5 on distribution_sheet (i.e. Row 5), VBA should check this value against stock_sheet!F5 while looping thru publication names (E5 to LastRow)

If user enters value in cells G5, I5, K5,...,BO5 on distribution_sheet (i.e. Row 5), VBA should check this value against stock_sheet!G5 while looping thru publication names (E5 to LastRow)

Screenshot-1 -> This is stock sheet with stock values **where row numbers may vary **

Screenshot-2 -> The following code works on this sheet

Screenshot-3 -> The code I've does not work in this sheet with language options (Tr, En)

Any help would be greatly appreciated.

Best regards.

Here is the current code I've so far

    Private Sub Worksheet_Change(ByVal Target As Range)
    Dim cell As Range
        Application.EnableEvents = False
        For Each cell In Target
            For i = 13 To 31
                If Not Application.Intersect(cell, Range("F" & (i) & ":BO" & (i))) Is Nothing Then
                    If Not IsNumeric(cell.Value) Or (cell.Value) > Worksheets("stock_sheet").Range("F" & (i)).Value Then
                        MsgBox Worksheets("stock_sheet").Range("E" & (i)).Value & vbCrLf & " please enter a number between 1 and " & Worksheets("stock_sheet").Range("F" & (i)).Value & _
                        " for this publication!", vbCritical, "ERROR"
                        cell.Value = vbNullString
                        cell.Select
                    End If
                End If
            Next i
        Next cell
        Application.EnableEvents = True
    End Sub

Answer 1

Use the value in row 4 of the target column to select the column on the stock_sheet.

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim cel As Range, rngStock As Range
   
    If Application.Intersect(Target, Range("F5:BO16")) Is Nothing Then
        Exit Sub
    End If
    
    Application.EnableEvents = False
    For Each cel In Target
        ' Tr
        Set rngStock = Worksheets("Stock_sheet").Range("F" & cel.Row)
        ' get language from row 4
        If Cells(4, cel.Column) = "En" Then
            Set rngStock = rngStock.Offset(, 1)
        End If
        
        If Not IsNumeric(cel.Value) Or (cel.Value > rngStock.Value) Then
            MsgBox "Please enter a number between 1 and " & rngStock.Value & _
                    " for this publication!", vbCritical, "ERROR"
            cel.Value = vbNullString
            cel.Select
        End If
    Next
    Application.EnableEvents = True
End Sub