compare data from two different sheets in same workbook

hello all,

i am facing problems. need your help on below problem.

i do have one workbook. please find attached example workbook. in that workbooks i do have 4 sheets that is fpl,ldn,result matched and result unmatched. i need to compare fpl col c to ldn col d. if cell is matching perfectly then then macro must highlight that row in green color in both sheets. if cell is not matching then macro will highlight that row in red color in both sheets. the another thing is i need to copy matched rows from both sheet and paste in result matched sheet. the same activity i must do with unmatched cell that is those cells which are not matching and we highlighted in red color must paste into result unmatched sheet.

guys every i received minimum 2000 rows data and its very tedious task to do that. i use excel 2010.

please help me.....

plzzzz help me...........

i am looking for code

These code would do much you have asked, but not everything. It onlly highlightes one sheet, but not both sheets. The find function works okay but not with large data, as the speed becomes an issue. I have also assumed , you want to colour and copy the entire row, but if you just wanted a single cell, just delete the word "Entire" from the code.

Sub find_events()
Dim c2 As Range, c As Range, LR As Long, um As Worksheet
Set um = Sheets("result unmatched")
    Application.ScreenUpdating = 0
    Application.EnableEvents = 0
    NR = 2
With Sheets("ldn")
    LR = .UsedRange.Rows.Count
    For Each c In .Range("D2:D" & LR)
        If Trim(c.Value) <> vbNullString Then
            Set c2 = Sheets("fpl").Columns(3).Find(c.Value, , , 1)
            If c2 Is Nothing Then
            c.EntireRow.Interior.Color = vbRed
                c.EntireRow.Copy um.Cells(NR, 1)
                NR = NR + 1
            End If
        End If
    Next
End With
Call find_events1
    Application.ScreenUpdating = 1
    Application.EnableEvents = 1
End Sub

Sub find_events1()
Dim c2 As Range, c As Range, LR As Long, um As Worksheet
Set um = Sheets("result matched")
    Application.ScreenUpdating = 0
    Application.EnableEvents = 0
    NR = 2
With Sheets("ldn")
    LR = .UsedRange.Rows.Count
    For Each c In .Range("D2:D" & LR)
        If Trim(c.Value) <> vbNullString Then
            Set c2 = Sheets("fpl").Columns(3).Find(c.Value, , , 1)
            If Not c2 Is Nothing Then
            c.EntireRow.Interior.Color = vbGreen
                c.EntireRow.Copy um.Cells(NR, 1)
                NR = NR + 1
            End If
        End If
    Next
End With
    Application.ScreenUpdating = 1
    Application.EnableEvents = 1
End Sub

hi ab33. you given me xmas gift. thanks a lot. u are real guru of vba. thanks a lot..... wish you happy xmas n new year............

You are welcome!
Thanks for kind words and wish you the same too!

Winon,
No at all, in fact quite the opposite, it can only enhance my knowledge.
Wish you the same!

thanks. i am so sorry because i need one amendment in this. code must copy matched rows in result matched sheet. it means the cell which is matching in ldn and fpl should be there in result matched sheet. it will be very very helpful for me. you guys already taken lots of effort i know that but i dont have any choice. i must tell you this.

no no your previous code was totally fine. i just need to paste rows in matched result sheet. for example in ldn sheet we have 200 number and in fpl sheet we have 200 number. i need both 200 number rows from both sheet pasted in matched sheet.

Akulka,
I am now compeltley lost you. I do not know which 200 rows you are referring to. My first code copies all your data, regardless on the number of rows you may have. You need to show me with a sample what you wish to do and what is missing from my code?

sorry. forgive me. plz find attached file.

The matched sheet has now values from both sheets, but I can not do the same for the unmatched as the name says, if they values do not match from both sheets, I can not copy from both sheets either.
It is really hard to code as you have many blanks with just values in a column, it would be easier to use the next empty row, but most of your data are empty. I have also copied the entire row in case you wanted the other rows too. If you just wanted to copy only the value, replace these lines

 c.Resize(, 7).Copy um.Cells(NR, 4).Resize(, 7)

with

 c.Copy um.Cells(NR, 4)

and these two lines

  c.Resize(, 7).Copy um.Cells(NR, 4).Resize(, 7)
                  c2.Resize(, 7).Copy um.Cells(NR + 1, 4).Resize(, 7)

with

  c.Copy um.Cells(NR, 4)
                  c2.Copy um.Cells(NR + 1, 4)

Sub find_events()
Dim c2 As Range, c As Range, LR As Long, um As Worksheet
Set um = Sheets("result unmatched")
    Application.ScreenUpdating = 0
    Application.EnableEvents = 0
    NR = 3
With Sheets("ldn")
    LR = .UsedRange.Rows.Count
    For Each c In .Range("D2:D" & LR)
        If Trim(c.Value) <> vbNullString Then
            Set c2 = Sheets("fpl").Columns(3).Find(c.Value, , , 1)
            If c2 Is Nothing Then
            c.EntireRow.Interior.Color = vbRed
                 c.Resize(, 7).Copy um.Cells(NR, 4).Resize(, 7)
                NR = NR + 1
            End If
        End If
    Next
End With
Call find_events1
    Application.ScreenUpdating = 1
    Application.EnableEvents = 1
End Sub

Sub find_events1()
Dim c2 As Range, c As Range, LR As Long, um As Worksheet
Set um = Sheets("result matched")
    Application.ScreenUpdating = 0
    Application.EnableEvents = 0
    NR = 3
With Sheets("ldn")
    LR = .UsedRange.Rows.Count
    For Each c In .Range("D2:D" & LR)
        If Trim(c.Value) <> vbNullString Then
            Set c2 = Sheets("fpl").Columns(3).Find(c.Value, , , 1)
            If Not c2 Is Nothing Then
            c.EntireRow.Interior.Color = vbGreen
                c.Resize(, 7).Copy um.Cells(NR, 4).Resize(, 7)
                  c2.Resize(, 7).Copy um.Cells(NR + 1, 4).Resize(, 7)
                NR = NR + 1
            End If
        End If
    Next
End With
    Application.ScreenUpdating = 1
    Application.EnableEvents = 1
End Sub