Comparing cells in two worksheets and doing some action

[rinijg]

I have a workbook with three worksheets
Sheet 1: Column 1- name column 2- check1 column 3- check 2
Sheet2: column 1- name column2- 10 of that row and the next 2 rows would contain some details of this name

I have to compare check1 and check2, and for whatever names check1 is not equal to check2, I have to make the rows of that name in sheet2 red in colour and copy these rows to sheet3.

following is the code that i wrote. But this is not making the second comparison(comparing names in sheet1 and sheet2) properly.

Sub routing_change()

   Dim n As Long, a As Long, i As Long, k As Long, m As Long, l As Long, re As Long
    Dim j As Integer
    k = 12: m = 12: a = 1
    
    With Worksheets("Sheet1")
        n = .Range("A2", .Range("A2").End(xlDown)).Count
    End With
    
    With Worksheets("Sheet2")
        re = .Range("B2", .Range("B2").End(xlDown)).Count
    End With
For i = 2 To n + 1
If StrComp(Worksheets("Sheet1").Cells(i, 2), Worksheets("Sheet1").Cells(i, 4)) = 0 Then
Else
For j = 2 To re + 1 Step 3
If StrComp(Worksheets("Sheet1").Cells(i, 3), Worksheets("Sheet2").Cells(j, 2)) = 0 Then
Worksheets("Sheet2").Activate
   With Rows(j & ":" & j).Interior
        .ColorIndex = 3
        .Pattern = xlSolid
    End With
   Rows(j+1&":"&j+1).Interior
     .ColorIndex = 3
       .Pattern = xlSolid
   End With
   Rows(j+2&":"&j+2).Interior
        .ColorIndex = 3
       .Pattern = xlSolid
   End With
    Exit For
    End If
    Next j
    End If
    Next i
end Sub

Can someone help me in this? Please

 

[venkat1926]

"column2- 10 of that row and the next 2 rows would contain some details of this name"

this is not clear. do you mean to say each name has 3 rows. or is there any typo in this statement.



what is the reason for
k = 12: m = 12
in the macro

Your macro may be correct but because I am not able to see the whole logic I am giving you another macro based on my understanding of sheet 2.
It would have been easier had you posted a truncated version of sheet1 and shee2. any how try this macro on A COPY OF YOUR FILE (NOT ORIGINAL) AND POST YOUR COMMENTS.
In the macro design stage introduce some msgbox(s) so that you can debug easily.l

Sub test()
Dim r As Range, c As Range, cfind As Range, x As String
With Worksheets("sheet1")
Set r = Range(.Range("A2"), .Range("A2").End(xlDown))
    For Each c In r
   ' c.Select
    x = c.Value
    If c.Offset(0, 1) = c.Offset(0, 2) Then GoTo nextc
        With Worksheets("sheet2").Columns("A:A")
        Set cfind = .Cells.Find(what:=x, lookat:=xlWhole)
        'MsgBox cfind.Address
        If cfind Is Nothing Then GoTo nextc
            With cfind.EntireRow
            .Cells.Interior.ColorIndex = 3
            .Copy
            End With  'c.entirerow
                With Worksheets("sheet3")
                .Cells(Rows.Count, "A").End(xlUp).Offset(1, 0).PasteSpecial
                                End With   'sheet3

        End With 'sheet2

nextc:
    Next c
    End With
End Sub