Compare two Excel worksheets in two files

I am trying to compare two Excel files:

PO File.xlsx
Inventory File.xlsx

Each file contains one worksheet with following columns
Item code, Item Name, Qty
I want to compare the Qty in the inventory file with the Qty in the PO File (PO is received from customers and items in it are to be checked whether available in stock (inventory) or not)

Inventory

I have the following code.

Sub CrossCheckInventoryPO()
    Dim InventoryWs As Worksheet
    Dim POWs As Worksheet
    Dim InventoryFileName As Variant
    Dim POFileName As Variant
    Dim CommonColumn As String
    Dim LastRow As Long

    ' Prompt the user to select the inventory file
    InventoryFileName = Application.GetOpenFilename("Excel Files (*.xls*), *.xls*")
    If InventoryFileName = "False" Then Exit Sub
    
    ' Prompt the user to select the PO file
    POFileName = Application.GetOpenFilename("Excel Files (*.xls*), *.xls*")
    If POFileName = "False" Then Exit Sub
    
    ' Set the common column header (e.g., "item code")
    CommonColumn = "Qty" ' Change this to match your actual column header
    
    ' Set the worksheets for inventory and PO
    Set InventoryWs = Workbooks.Open(InventoryFileName).Worksheets(1)
    Set POWs = Workbooks.Open(POFileName).Worksheets(1)
    
    ' Find the last row in the inventory and PO worksheets
    LastRow = InventoryWs.Cells(InventoryWs.Rows.Count, CommonColumn).End(xlUp).Row
    
    
    ' Loop through each row in the inventory worksheet
    For i = 2 To LastRow ' Assuming headers are in row 1
        ' Get the value from the common column in the inventory sheet
        Dim InventoryValue As Variant
        InventoryValue = InventoryWs.Cells(i, CommonColumn).Value
        
        ' Use VLOOKUP to find the corresponding value in the PO sheet
        Dim POValue As Variant
        On Error Resume Next ' Ignore errors temporarily
        POValue = Application.VLookup(InventoryValue, POWs.Range(CommonColumn & ":" & CommonColumn), 3, False)
        On Error GoTo 0 ' Reset error handling
        
        ' Check if an error occurred during the VLOOKUP
        If IsError(POValue) Then
            ' Do something when the value is not found in the PO sheet
            ' For example, highlight the row in red or display a message
            InventoryWs.Rows(i).Interior.Color = RGB(255, 0, 0) ' Red color
        End If
    Next i
    
    ' Close the inventory and PO workbooks without saving changes
    InventoryWs.Parent.Close SaveChanges:=False
    POWs.Parent.Close SaveChanges:=False
    
    ' Inform the user that the process is complete
    MsgBox "Cross-checking completed!", vbInformation
End Sub

The items that are present in the PO file but not in the inventory file should be highlighted in red.
Plus items that are available in the inventory file but their Qty is less than what it is in the PO file should also be highlighted (may be in some different color).

Solution

Your code is nearly complete.

Changes

CommonColumn = "C" common column should be column name (index) instead of header name
Ensure that the 2nd parameter of VLookup begins from column A.
Apply a yellow highlight to indicate lower inventory levels.
Loop through PO worksheet, validate qty against inventory
Highligh and save the PO file.

Option Explicit

Sub CrossCheckInventoryPO()
    Dim InventoryWs As Worksheet
    Dim POWs As Worksheet
    Dim InventoryFileName As Variant
    Dim POFileName As Variant
    Dim CommonColumn As String
    Dim LastRow As Long
    ' Prompt the user to select the inventory file
    InventoryFileName = Application.GetOpenFilename("Excel Files (*.xls*), *.xls*")
    If InventoryFileName = "False" Then Exit Sub
    ' Prompt the user to select the PO file
    POFileName = Application.GetOpenFilename("Excel Files (*.xls*), *.xls*")
    If POFileName = "False" Then Exit Sub
    ' Set the common column header (e.g., "item code")
    CommonColumn = "C" ' **
    ' Set the worksheets for inventory and PO
    Set InventoryWs = Workbooks.Open(InventoryFileName).Worksheets(1)
    Set POWs = Workbooks.Open(POFileName).Worksheets(1)
    ' Find the last row in the inventory and PO worksheets
    LastRow = POWs.Cells(POWs.Rows.Count, CommonColumn).End(xlUp).Row
    ' Loop through each row in the inventory worksheet
    For i = 2 To LastRow ' Assuming headers are in row 1
        ' Get the value from the common column in the inventory sheet
        Dim InventoryValue As Variant
        Dim POValue As Variant
        POValue = POWs.Cells(i, CommonColumn).Value
        ' Use VLOOKUP to find the corresponding value in the PO sheet
        On Error Resume Next ' Ignore errors temporarily
        InventoryValue = Application.VLookup(InventoryValue, POWs.Range("A:" & CommonColumn), 3, False)
        On Error GoTo 0 ' Reset error handling
        ' Check if an error occurred during the VLOOKUP
        If IsError(InventoryValue) Then
            ' Do something when the value is not found in the PO sheet
            ' For example, highlight the row in red or display a message
            POWs.Rows(i).Interior.Color = RGB(255, 0, 0) ' Red color
        ElseIf POValue > InventoryValue Then ' **
            POWs.Rows(i).Interior.Color = RGB(255, 255, 0) ' Yellow color ' **'
        End If
    Next i
    ' Close the inventory and PO workbooks without saving changes
    InventoryWs.Parent.Close SaveChanges:=False ' **'
    POWs.Parent.Close SaveChanges:=True
    ' Inform the user that the process is complete
    MsgBox "Cross-checking completed!", vbInformation
End Sub

Utilizing a Dictionary object to enhance efficiency.

Loading all data into an array.
Implementing highlighting rules:

For cases of no inventory, highlighting the PO in red.
For cases of lower inventory, applying yellow highlights to both the PO and inventory.

Option Explicit
Sub CrossCheckInventoryPO()
    Dim InventoryWs As Worksheet
    Dim POWs As Worksheet
    Dim InventoryFileName As Variant
    Dim POFileName As Variant
    Dim CommonColumn As String
    Dim LastRow As Long, i as Long
    Dim arrInv, arrPO, objDic
    InventoryFileName = Application.GetOpenFilename("Excel Files (*.xls*), *.xls*")
    If InventoryFileName = "False" Then Exit Sub
    POFileName = Application.GetOpenFilename("Excel Files (*.xls*), *.xls*")
    If POFileName = "False" Then Exit Sub
    CommonColumn = "C" 
    Set InventoryWs = Workbooks.Open(InventoryFileName).Worksheets(1)
    Set POWs = Workbooks.Open(POFileName).Worksheets(1)
    ' Clean highligh
    InventoryWs.Cells.Interior.Pattern = xlNone
    POWs.Cells.Interior.Pattern = xlNone
    set objDic = CreateObject("scripting.dictionary")    
    ' Loading inventory data
    LastRow = InventoryWs.Cells(InventoryWs.Rows.Count, CommonColumn).End(xlUp).Row
    arrInv = InventoryWs.Range("A1:" & CommonColumn & LastRow).Value
    For i = 2 To LastRow 
        objDic(arrInv(i,1)) = Array(Val(arrInv(i,3)), i)
    Next
    ' Loading PO data
    LastRow = POWs.Cells(POWs.Rows.Count, CommonColumn).End(xlUp).Row
    arrPO = POWs.Range("A1:" & CommonColumn & LastRow).Value
    Dim sKey As Variant
    For i = 2 To LastRow         
        sKey = arrPO(i,1)
        If objDic.Exists(sKey) Then
            ' Inventory exist
            If arrPO(i, 3) > objDic(sKey)(0) Then
                ' Low inventory
                POWs.Rows(i).Interior.Color = RGB(255, 255, 0) 
                InventoryWs.Rows(objDic(sKey)(1)).Interior.Color = RGB(255, 255, 0) 
            End IF
        Else
            ' No inventory
            POWs.Rows(i).Interior.Color = RGB(255, 0, 0) 
        End If
    Next i
    ' Save workbook
    InventoryWs.Parent.Close SaveChanges:=True 
    POWs.Parent.Close SaveChanges:=True
    MsgBox "Cross-checking completed!", vbInformation
End Sub