Select matching cell and insert value 4 cells to the left of selected cell?

Hello all,

This is pulling from something that was already done by walruseggman on the forums. He helped me make a macro that searched for partial matches between two columns in separate different worksheets and copy the interior color from one to the other if there was a match. You can see that post here: Old Postl

Heres the current code Im using:

Sub TomToms()

Set ws1 = Worksheets("PO Info Checklist")
Set ws2 = Worksheets("Closed Jobs List")

Set CheckRange = ws2.Range("A1:A" & ws2.Cells(Rows.Count, "A").End(xlUp).Row)
Set SearchRange = ws1.Range("J1:J" & ws1.Cells(Rows.Count, "J").End(xlUp).Row)

For Each cell In CheckRange
SearchItem = "*" & cell.Value & "*"
Set c = SearchRange.Find(SearchItem)
    If Not c Is Nothing Then c.Interior.Color = cell.Interior.Color 
Next

End Sub

Here's what I would like to do. in ws1 column N is a blank column for dates. In ws2 column C has dates filled in. I would like for each match in the previous code to pull the date from column C in ws2 and put it into column N for ws1.

I tried some stuff on my own, but I kept running into roadblocks

Any help is appreciated.

Try this:

Sub TomToms()

Set ws1 = Worksheets("PO Info Checklist")
Set ws2 = Worksheets("Closed Jobs List")

Set CheckRange = ws2.Range("A1:A" & ws2.Cells(Rows.Count, "A").End(xlUp).Row)
Set SearchRange = ws1.Range("J1:J" & ws1.Cells(Rows.Count, "J").End(xlUp).Row)

For Each cell In CheckRange
SearchItem = "*" & cell.Value & "*"
Set c = SearchRange.Find(SearchItem)
    If Not c Is Nothing Then
        c.Interior.Color = cell.Interior.Color
        cell.Offset(, 13).Value = c.Offset(, -7).Value
    End If
Next

End Sub

Thanks for the quick reply, but unfortunately, it doesn't seem to do anything :/ But it might be due to another problem. It looks like my code doesn't actually highlight ALL the rows :/ I'm not sure why.

I have attached the workbook I'm working with, with all (non important values removed of course) the code is in the mainmodule. If you look at Column J in PO Info Checklist worksheet. You'll see almost right away that for example on Line 23 its highlighted red cause 1117 is highlighted red in the Closed Jobs List but cells 24-28 in column J should also be red cause they are also 1117 but they are not:/

I'm not sure whats going on there.

Po Checklist 2.xlsm

Sorry ignore this, can't read! Will come back.

Works perfectly, thankyou