I’m currently using VBA to check if content of column E (say row X) matches with that of content in column B (say row Y). Let’s say value of X:E is ‘a’ then it will find if ‘a’ exists in any of the rows in column B. Let’s say there are 4 rows that have value ‘a’. Based on another condition, it will find row Y out of these 4 rows. Let’s say 3rd row is my required row Y. But code is giving the output as 2nd row i.e. the row just previous to the required row. How do I solve it?

The output expected is ‘a’ of column E in row X matches with ‘a’ of column B in row Y. Below is the code:

Sub CaseOneTwoandFour()
    Dim ws As Worksheet
    Dim lastRow As Long
    Dim componentNames As New Collection
    Dim cell As Range
    Dim item As Variant
    Dim foundRow As Variant
    Dim i As Long
    Dim j As Long
    Dim cellValue As Variant
    Dim matchRow As Long
    Dim matchFound As Boolean
    Dim tempValue As Variant
    Dim initialRowColor As Long
    
    ' Set the worksheet
    Set ws = ThisWorkbook.Sheets("Sheet1")
    
    ' Find the last row
    lastRow = ws.Cells(ws.Rows.Count, 1).End(xlUp).Row
    
    ' Loop through each cell in column A and add unique component names to the collection
    On Error Resume Next
    For Each cell In ws.Range("A5:A" & lastRow)
        If cell.Value <> "" Then
            componentNames.Add cell.Value, CStr(cell.Value)
        End If
    Next cell
    On Error GoTo 0
    
    For Each item In componentNames
        ' Find the row with the specified component name in column A
        foundRow = Application.Match(item, ws.Range("A5:A" & lastRow), 0)
        If IsError(foundRow) Then Exit Sub
        
        ' Loop through rows below the found component name row
        For i = 5 To lastRow
            cellValue = ws.Cells(i, 3).Value
            
            ' Check if columns D and E or columns F and G have content
            If (Len(Trim(ws.Cells(i, 4).Value)) > 0 And Len(Trim(ws.Cells(i, 5).Value)) > 0) Or _
               (Len(Trim(ws.Cells(i, 6).Value)) > 0 And Len(Trim(ws.Cells(i, 7).Value)) > 0) Then
                
                matchFound = False
                
                ' Check if contents from either column E (5) or G (7) match any cell in column B (2)
                For j = 5 To ws.Cells(ws.Rows.Count, "B").End(xlUp).Row
                    If StrComp(ws.Cells(j, 2).Value, ws.Cells(i, 5).Value, vbBinaryCompare) = 0 Or _
                       StrComp(ws.Cells(j, 2).Value, ws.Cells(i, 7).Value, vbBinaryCompare) = 0 Then
                        
                        If Not ws.Rows(j).Find(What:=cellValue, LookIn:=xlValues, LookAt:=xlWhole) Is Nothing Then
                            matchRow = j
                            matchFound = True
                            Exit For
                        End If
                    End If
                Next j
                
                If matchFound Then
                    ' Ensure matchRow is set before trying to access it
                    If matchRow > 0 Then
                        If Len(Trim(ws.Cells(matchRow, 5).Value)) > 0 Then
                            tempValue = ws.Cells(matchRow, 5).Value
                        ElseIf Len(Trim(ws.Cells(matchRow, 7).Value)) > 0 Then
                            tempValue = ws.Cells(matchRow, 7).Value
                        End If
                        
                        initialRowColor = ws.Cells(i, 8).Interior.Color
                        If StrComp(ws.Cells(i, 2).Value, tempValue, vbBinaryCompare) = 0 Then
                            ws.Cells(i, 8).Interior.Color = RGB(0, 255, 0) ' Green color for initial row
                            ws.Cells(matchRow, 8).Interior.Color = RGB(0, 255, 0) ' Green color for matched row
                        Else
                            ws.Cells(i, 8).Interior.Color = RGB(255, 0, 0) ' Red color
                            ws.Cells(i, 9).Value = "Wrong Connection"
                        End If
                    End If
                Else
                    ws.Cells(i, 8).Interior.Color = RGB(255, 0, 0) ' Red color
                    ws.Cells(i, 9).Value = "Wrong Connection"
                End If
            End If
            
            ' Highlight red if E or G has content but D or F does not respectively
            If (Len(Trim(ws.Cells(i, 5).Value)) > 0 And Len(Trim(ws.Cells(i, 4).Value)) = 0) Or _
               (Len(Trim(ws.Cells(i, 7).Value)) > 0 And Len(Trim(ws.Cells(i, 6).Value)) = 0) Then
                ws.Cells(i, 8).Interior.Color = IIf(StrComp(item, "connector", vbBinaryCompare) = 0, RGB(255, 255, 0), RGB(255, 0, 0))
                If ws.Cells(i, 1).Value = "connector" Then ws.Cells(i, 8).Interior.Color = RGB(255, 255, 0)
                ws.Cells(i, 9).Value = "One of the connections is absent for Connector"
            End If
            
            ' Highlight red if all columns D, E, F, and G are empty
            If Len(Trim(ws.Cells(i, 4).Value)) = 0 And Len(Trim(ws.Cells(i, 5).Value)) = 0 And _
               Len(Trim(ws.Cells(i, 6).Value)) = 0 And Len(Trim(ws.Cells(i, 7).Value)) = 0 Then
                ws.Cells(i, 8).Interior.Color = RGB(255, 255, 0) ' Red color
                ws.Cells(i, 9).Value = "There is interaction with non-terminal component (No Producer or Consumer)"
            End If
            
            ' If all columns D, E, F, and G have content, give red color
            If Len(Trim(ws.Cells(i, 4).Value)) > 0 And Len(Trim(ws.Cells(i, 5).Value)) > 0 And _
               Len(Trim(ws.Cells(i, 6).Value)) > 0 And Len(Trim(ws.Cells(i, 7).Value)) > 0 Then
                ws.Cells(i, 8).Interior.Color = RGB(255, 0, 0) ' Red color
            End If
            
        Next i
    Next item
End Sub