Match 2 Columns in two sheets; copy an offset Column of matched Row to Sheet 1

Hello,

Need a VBA code to match 2 Columns in two sheets; then copy an offset Column from the matched Row of sheet2 to end of the corresponding matching row in sheet1. Please see the sheet "Summary" of the attached workbook to get a clear picture.

Sheets 1 & 2 both have similar data in Columns A through F, with no more than 300 Rows of Data.
First, need to delete Rows containing 000 in Column D of both sheets.
Need to match Columns "D" of sheets 1 and 2. For the matches found,
- Copy Column A from the corresponding row of sheet 2 to Column G of the matching Row of sheet 1.
- In Sheet 1, write "Existing" in Column H for the matches, and "New" in Column H for the Rows that did not have a match.
- If newly written Column G = Column A (ex: G5 = A5), highlight the corresponding Column H (ex: H5)

If it is convenient to create a third sheet for results instead of writing to Sheet 1, that will be okay.

I found a similar VBA code that compared the first columns and replaced the entire row. But my knowledge of VBA is so minuscule, I was not able to alter it to suit my needs.

Many thanks in advance.

Try

Please Login or Register  to view this content.

Jindon, The code works exactly the way I want. Thank you very much. Much appreciated!!

Originally Posted by jindon

Try

Please Login or Register  to view this content.

Jindon, The only hitch is that Column H does not highlight when Column A = Column G. In the workbook example, H7 as G7 = A7. Thought I could figure out, sorry, just couldn't.

OOps, can you change

Please Login or Register  to view this content.

to

Please Login or Register  to view this content.

Jindon, It still does not highlight. Can there be anything associated with the use of "With .Columns(.Columns.Count)" line?? Thanks

?........................

Thanks for the response again. I tried the workbook you reattached. All worked except the highlighting. If it highlighted when you ran, perhaps something to do with my version (2007). I can achieve the highlighting with normal conditional formatting. I appreciate for the code. Regards!!

I don't think there is a version difference between 2007 & 2013 reg Conditional Formatting.

If you say you can add conditional formatting manually, what was the formula then?