Dear friends.below macro work fine for me..i need some modification on row highlighting part..this macro compere first sheet column "D" with second sheet column "D" .if any cell is matched then first sheet that matched cell row highlighting.but i need to highlight that second sheet matched cell row also......pls help me......
Code:
Sub MatchData()
Dim Cell As Range
Dim FoundIt As Range
Dim LastCol As Long
Dim LastRow As Long
Dim Rng1 As Range
Dim Rng2 As Range
Dim Wks1 As Worksheet
Dim Wks2 As Worksheet
Set Wks1 = Worksheets("pending orders")
Set Wks2 = Worksheets("prr")
Set Rng1 = Wks1.Range("D8")
Set Rng2 = Wks2.Range("D9")
LastRow = Wks1.Cells(Rows.Count, Rng1.Column).End(xlUp).Row
If LastRow < Rng1.Row Then Exit Sub
Set Rng1 = Rng1.Resize(LastRow - Rng1.Row + 1, 1)
LastRow = Wks2.Cells(Rows.Count, Rng2.Column).End(xlUp).Row
If LastRow < Rng2.Row Then Exit Sub
Set Rng2 = Rng2.Resize(LastRow - Rng2.Row + 1, 1)
Wks1.Cells(7, "P").EntireColumn.Insert
Wks1.Range("P7").Value = Fix(Now())
Wks1.Range("P7").NumberFormat = "dd-mm-yy"
LastCol = Wks1.Cells(7, Columns.Count).End(xlToLeft).Column
For Each Cell In Rng1
Set FoundIt = Rng2.Find(Cell, , xlValues, xlWhole, xlByRows, xlNext, False, False)
If Not FoundIt Is Nothing Then
Cell.Offset(0, 12).NumberFormat = FoundIt.Offset(0, 2).NumberFormat
Cell.Offset(0, 12).Value = FoundIt.Offset(0, 2).Value
Cell.Offset(0, -3).Resize(1, LastCol).Interior.ColorIndex = 6
End If
Next Cell
End Sub
