How to select table cell in the if function in VBA excel?

Question

I have a stock management table in excel sheet. Demo here: Demo photo of this table

I want to create a code in VBA that comes In stock column and will test the whole cells. If any cell.Value < 2 and >0 then go to 4 cells backward take the value of this cell and show a message box "backward cell.value and stock not available".

---

I tired to make this please help me.

Answer 1

There are a few ways to select table cells, here is one'

Option Explicit

Sub CheckStock()
    Dim r As Long, n As Long, i As Integer, p As Integer
    Dim s As String

    With Sheet1.ListObjects("Table1")
        i = .ListColumns("In stock").Index
        p = .ListColumns("Product Name").Index

        For r = 1 To .DataBodyRange.Rows.Count
            n = .DataBodyRange(r, i)
            If n > 0 And n < 2 Then
                s = s & vbCrLf & .DataBodyRange(r, p)
            End If
        Next
    End With
    If Len(s) > 0 then 
        MsgBox "Products not available :" & s, vbExclamation
    End If
End Sub