Vba - compare all values in two column

QuestionEdit

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

AnswerEdit

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.