# Vba - compare all values in two column

Last Edited By Krjb Donovan
Last Updated: Mar 05, 2014 10:00 PM GMT

## Question

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

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

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

``` C       D
```

house dog

Master row 8

``` Y       Z
```

house dog

then this would be a match

```  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.