macro that will match two different column from two different sheets

[p9j123]

Hello Excel Masters,

I badly need help here, I am working on a file that has two sheets.

I need a macro that will match EMPID and PROCODE in Sheet 1(April (2)) with Sheet 2 (Compre List), if found the entire row in Sheet 2 should be deleted then the EMPID[C] and PROCODE[D] should be posted in the next blank row of Sheet 1 ( and [D] of Sheet 1).
Few things to note.

1. Above square brackets represent column letters.
2. I need the macro to look for the first blank row between row 13 to row 25 only, not from the bottom of the sheet.
3. The macro will be run from different sheet (April (1), April (2), April (3), etc.)

Please see the link of the workbook for your reference.

https://app.box.com/s/tw1pzo3hhn1dbwmionpf

 

[p9j123]

Alternatively, can we move the duplicate data to another sheet? (let say the name of the new sheet is "Duplicate") instead of just highlighting it?

I understand you provided an option to delete the entire row, can we modify it to move the data to the "Duplicate" sheet instead?

 

[NicholasP]

Alternatively, can we move the duplicate data to another sheet? (let say the name of the new sheet is "Duplicate") instead of just highlighting it?

I understand you provided an option to delete the entire row, can we modify it to move the data to the "Duplicate" sheet instead?

Ok, so I added a counting variable ("duprw") which you can see starts @ 1 (you can change that to whatever you want) and uses the A column (column 1). If you want it to copy *after* the cells get highlighted, just move the c.entirerow.copy and the dup = dup + 1 to right above where I have the row getting deleted commented out.

Sheets(sht).Activate

Dim duprw As Long
duprw = 1

For Each cell In rng

Set c = CompreRng.Find(cell, , , xlWhole)
    If Not c Is Nothing Then
        If c.Offset(0, -1) = cell.Offset(0, -2) Then
            
            c.EntireRow.Copy Sheets("Duplicate").Cells(duprw, 1)
            duprw = duprw + 1
            c.Interior.ColorIndex = 3
            c.Copy Cells(lstrw, 4)
            
            c.Offset(0, -1).Interior.ColorIndex = 3
            c.Offset(0, -1).Copy Cells(lstrw, 2)
            c.EntireRow.Interior.ColorIndex = 3
            lstrw = lstrw + 1

            ''c.EntireRow.Delete shift:=xlUp
        End If
    End If

Next

 

[p9j123]

Thank you Nick, I will try it again.