Macro To find match Value from other clomn and highlight in yellow

**tariqnaz2005** · 06-13-2012, 11:17 AM

Dear All..Hope all is fine..

Format is hereby attached....FORMAT.xlsx

My work is to reconciol accounts..I need ur help to give me a macro to find the match number (Full or Last 4 digits) and highlight both of it. and also as per attached second sheet (if Possible) but first sheet is so important).

Note: I want to find part number in Column J from Coumn D..thanks
Thanks in advance.

**tariqnaz2005** · 06-16-2012, 03:59 AM

Hello all..

Pls some one can help me in this regards...thanks

**StevenM** · 06-16-2012, 09:42 AM

Backup your data.
Place the code below in a standard module (if you don't know how, ask).
Run: FindPartNumbersInD

Sub FindPartNumbersInD()
    Dim nLastRow As Long, nRow As Long
    Dim sPartNumber As String
    Dim rgCell As Range
    
    nLastRow = Cells(Rows.Count, "J").End(xlUp).Row
    For nRow = 2 To nLastRow
        sPartNumber = Cells(nRow, "J")
        If Len(sPartNumber) Then
            Set rgCell = Range("D:D").Find(What:=sPartNumber, _
                           LookIn:=xlValues, _
                           Lookat:=xlPart, _
                           SearchOrder:=xlByRows, _
                           SearchDirection:=xlNext, _
                           MatchCase:=True)
            If Not rgCell Is Nothing Then
                Cells(nRow, "J").Interior.Color = 65535
                Cells(rgCell.Row, "D").Interior.Color = 65535
            End If
            If Len(sPartNumber) > 4 Then
                sPartNumber = Right$(sPartNumber, 4)
                Set rgCell = Range("D:D").Find(What:=sPartNumber, _
                           LookIn:=xlValues, _
                           Lookat:=xlPart, _
                           SearchOrder:=xlByRows, _
                           SearchDirection:=xlNext, _
                           MatchCase:=True)
                If Not rgCell Is Nothing Then
                    Cells(nRow, "J").Interior.Color = 65535
                    Cells(rgCell.Row, "D").Interior.Color = 65535
                End If
            End If
        End If
    Next nRow
End Sub

I suspect that this isn't by itself going to be helpful, but perhaps it is a start.