How do I compare two tables with the same column definitions in different schemas?

Vba - compare all values in two column

• QUESTION: I would like to ask you for help. I am trying to find some values from my source sheet in target one. If the source's value is in target available then do nothing, if not, then insert value "1" into source's column A (same row). Then continue with next value... More details are as follows: source sheet - Madox | values from column C | insert "1" into column A (if is not find in target sheet) target sheet - Master | values from column Z Best Lucy ANSWER: Lucy, VBA would be overkill for this. You can do it with a simple formula in A2 of Madox =if(countif(Master!$Z:$Z,$C2)>0,"",1) then drag fill down column A of Madox if you want VBA Sub ABC() Dim sh1 As Worksheet, sh2 As Worksheet, r1 As Range Dim r2 As Range, cell As Range Set sh1 = Worksheets("Madox") Set sh2 = Worksheets("Master") Set r1 = sh1.Range(sh1.Cells(2, "C"), sh1.Cells(sh1.Rows.Count, "C").End(xlUp)) Set r2 = sh2.Range(sh2.Cells(2, "Z"), sh2.Cells(sh2.Rows.Count, "Z").End(xlUp)) For Each cell In r1 If Application.CountIf(r2, cell) = 0 Then cell.Offset(0, -2) = 1 End If Next End Sub that assumes that you have headers in row 1 and your data starts in row 2 in each sheet. Code was tested and worked fine for me. ---------- FOLLOW-UP ---------- QUESTION: sorry, I thought that I am able to adapt your macro for my needs, but I am not although it looks easy. I need to compare two values from source with target one. C and D (source sh) with Y and Z (target sh). If are the values same, then insert value "1" into column A and change of the cell's background (cell.Offset(0, ...).Interior.ColorIndex = 4. I would appreciate it if you could help me again. Lucy

• Answer:

  Lucy, so you want to compare say C2 to column Y and D2 to column Z and they must match cells in the same row in Madox row 2 C D house dog Master row 8 Y Z house dog then this would be a match Madox row 2 C D house Dog Master row 8 C D house car Master row 9 C D money Dog this would not be a match and 1 would be placed in column A is that correct. --- Revision -- I guess you said if they are the same/match to put in the 1 which is different from the original question ------- here is the revised code ---- Sub ABCD_YZ() Dim sh1 As Worksheet, sh2 As Worksheet, r1 As Range Dim r2 As Range, cell1 As Range, cell2 As Range Dim bFound As Boolean Set sh1 = Worksheets("Madox") Set sh2 = Worksheets("Master") Set r1 = sh1.Range(sh1.Cells(2, "C"), sh1.Cells(sh1.Rows.Count, "C").End(xlUp)) Set r2 = sh2.Range(sh2.Cells(2, "Y"), sh2.Cells(sh2.Rows.Count, "Y").End(xlUp)) For Each cell1 In r1 bFound = False For Each cell2 In r2 If cell1 = cell2 And cell1.Offset(0, 1) = cell2.Offset(0, 1) Then bFound = True Exit For End If Next cell2 If bFound = True Then With cell1.Offset(0, -2) .Value = 1 .Interior.ColorIndex = 4 End With End If Next cell1 End Sub this assumes no blank cells embedded in the ranges being checked. If that isn't what you want, then please clarify the situation. code worked for me given the stated assumptions.