Search 3 columns in a range of rows in nested loop

Question

Hi I have several sheets and I have to do a find and highlight each row in every sheet using this criteria

In my sheet I grouped data in 2 sections (same sheet)
**Prod data**                                                   **TEST Data**
col A   B  C  D  E  F                                     col G H I J K L         MATCH RESULT COL
ROWS 1 - 100                                              ROWS 1 - 170

I need to search prod data for each row for cols A C F in test data for col G I L in all 170 rows, if all matches (A=G, B=I, F=L) then either highlight in green if not in red. Or in a last empty cell print something "Match" with green highlight.

The solutions could be a macro or formula. I have to use it in many sheets with different col selections.

So, what exactly is your question? So far you have only presented your task. I hope you are not expecting somebody here to do your job. — Tom Brunberg

Builder Builder · Accepted Answer · 2020-08-21T03:09:52

I did it and got my answer here

It was my requirement Left side is production records and right side is Test server records. We need to compare visually so I extract both table from both servers and pasted in excel sheet and this macro did it all. You just need to manipulate correct column numbers for sheet. If you have any question, please dont hesitate and send your questions.

Sub FND1()
Dim t As Integer
Dim p As Integer
Dim icolor As Integer
Dim tval1 As Variant
Dim tval2 As Variant
Dim tval3 As Variant
Dim bFoundinProd As Boolean

NumRowsProd = 8  'Range("A1", Range("A1").End(xlDown)).Rows.Count
NumRowsTest = 10 'Range("F1", Range("F1").End(xlDown)).Rows.Count

      Application.ScreenUpdating = False
      ' Set numrows = number of rows of data.
      
      ' Establish "For" loop to loop "numrows" number of times.
    With Worksheets(1)
      For t = 1 To NumRowsTest
        tval1 = .Cells(t, 6).Value
        tval2 = .Cells(t, 7).Value
        tval3 = .Cells(t, 8).Value
        
       For p = 1 To NumRowsProd
          bFoundinProd = False
          If tval1 = .Cells(p, 1).Value And _
                tval2 = .Cells(p, 2).Value And _
                tval3 = .Cells(p, 3).Value Then
                bFoundinProd = True
                .Cells(p, 1).Interior.ColorIndex = 4  '3 red 4 green  5 blue
                .Cells(p, 2).Interior.ColorIndex = 4
                .Cells(p, 3).Interior.ColorIndex = 4
                Exit For
          End If
        Next p

         If bFoundinProd Then
            .Cells(t, 10).Value = "Found in Production. row = " & CStr(p)
            icolor = 4  ' green
         Else
            icolor = 3 ' red
            .Cells(t, 10).Value = "Not Found in Production"
         End If
         
        .Cells(t, 6).Interior.ColorIndex = icolor  '3 red 4 green  5 blue
        .Cells(t, 7).Interior.ColorIndex = icolor
        .Cells(t, 8).Interior.ColorIndex = icolor
        .Cells(t, 9).Interior.ColorIndex = icolor
        .Cells(t, 10).Interior.ColorIndex = icolor
         
      Next t
    End With
    Worksheets("Sheet1").Range("J1").Columns.AutoFit
        
      Application.ScreenUpdating = True
End Sub

Search 3 columns in a range of rows in nested loop

1 Answers