vba - how can I compare 3 different columns of data in 3 different worksheets and highlight them in excel 2007?

Question

so I have two worksheets with data, I want to compare column A of worksheet 1 to column A of worksheet 2 . Worksheet 1 is a template and worksheet 2 is a data report. Column A for both worksheets are item #s, worksheet 1 updates item #s and data from the report in worksheet 2. I want to create a formula or I guess vba code (I know nothing about VBA) whatever I can that will compare the item#s. I want the formula/code to pull the data from the report in worksheet 2 to the corresponding item #s in the template of worksheet 1 and high light any new item #s in the report(column A) of worksheet 2 that are not in worksheet 1 and then add those new high lighted item #s and its data to worksheet 1.

The range of data in column A of worksheet 1 is less than the range of data in column A of worksheet 2 that im trying to compare and pull from.

I thought about creating a IF formula in worksheet 2 to show me which item number exist, which are new but because the range of data in column A of worksheet 1 is less than column A of worksheet 2 I end getting an error value, I think creating a vba may be better but im not sure . Any advice, please give step by step detail/image of what to do. So far in general for the template I have a vlookup to pull the data BUT I need to know which items are new and highlight/pull them. Also if I could either delete or label the items numbers that is discontinued as discontinued that would be great. I think so far when I do a vlookup the items that are in worksheet 1 and not worksheet 2 come up as #N/A are the discontinued items. I have excel 2007, I hope you can help.

Sample Sheet1 and Sheet2

Output after updating using Vlookup

I am trying to compare three different columns of data in three worksheets and highlight the differences using VBA. I am very new to VBA and I don't know a lot about programming. So far this is what I've done:

worksheet1:

Sub compare_cols()
 Dim myRng As Range
 Dim lastCell As Long
 'Get the last row
 Dim lastRow As Integer
 lastRow = ActiveSheet.UsedRange.Rows.Count
 'Debug.Print "Last Row is " & lastRow
 Dim c As Range
 Dim d As Range
 Application.ScreenUpdating = False
 For Each c In Worksheets("worksheet1").Range("A2:A" & lastRow).Cells
 For Each d In Worksheets("worksheet2").Range("A2:A" & lastRow).Cells
 c.Interior.Color = vbGreen
 If (InStr(1, d, c, 1) > 0) Then
 c.Interior.Color = vbWhite
 Exit For
 End If
 Next
 Next
 For Each c In Worksheets("worksheet2").Range("A2:A" & lastRow).Cells
 For Each d In Worksheets("worksheet1").Range("A2:A" & lastRow).Cells
 c.Interior.Color = vbYellow
 If (InStr(1, d, c, 1) > 0) Then
 c.Interior.Color = vbWhite
 Exit For
 End If
 Next
 Next
Application.ScreenUpdating = True
End Sub
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
End Sub
Worksheet 2:
Sub compare_cols()
 Dim myRng As Range
 Dim lastCell As Long
 'Get the last row
 Dim lastRow As Integer
 lastRow = ActiveSheet.UsedRange.Rows.Count
 'Debug.Print "Last Row is " & lastRow
 Dim c As Range
 Dim d As Range
 Application.ScreenUpdating = False
 For Each c In Worksheets("worksheet2").Range("A2:A" & lastRow).Cells
 For Each d In Worksheets("worksheet3").Range("A2:A" & lastRow).Cells
 c.Font.Color = rgbRed
 If (InStr(1, d, c, 1) > 0) Then
 c.Font.Color = rgbBlack
 Exit For
 End If
 Next
 Next
 For Each c In Worksheets("worksheet3").Range("A2:A" & lastRow).Cells
 For Each d In Worksheets("worksheet2").Range("A2:A" & lastRow).Cells
 c.Interior.Color = vbRed
 c.Font.Color = rgbWhite
 If (InStr(1, d, c, 1) > 0) Then
 c.Interior.Color = vbWhite
 c.Font.Color = rgbBlack
 Exit For
 End If
 Next
 Next
Application.ScreenUpdating = True
End Sub  

.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
End Sub  

Problems I'm having:

• in comparing worksheet1 and worksheet2, data that is not in worksheet2 are highlighted green in worksheet1, and data that is not in worksheet1 are highlighted yellow in worksheet2.
• some of the data in worksheet2 for example are highlighted in yellow but are found in worksheet1, which shouldn’t happen. Then in comparing worksheet2 and worksheet3, items that are not in worksheet3 have a red colored font, in worksheet2, and items that are not in worksheet2 are highlighted red with a white font, in worksheet3.
• data in worksheet2 for example have a red colored font but are found in worksheet3, which shouldn’t happen.

Can you please tell me why my VBA code isn't working, or what I else I can do?

Answer 1

If you want to do this automatically - it's about using VBA. But you don't have any knowledge of it, so I will not give you ready VBA solution.

To achieve your goal you can use Remove duplicates. With this you will create list of unique items - this will be list of all items from workbook1 and workbook2. Later you can create column with vlookup function as you did before. If item was found you will get Qty as you wanted, if not you will get #N/D which you can highlight using conditional formatting.

I guess it should solve your problem without VBA.