# Macro comparing 2 columns in 2 sheets

#### alvbnp

##### Board Regular
Example data:

sheet1
1 | a
1 | b
2 | a
2 | b

sheet2
1 | a
1 | b
1 | c
2 | a
2 | b
3 | a

the important part is the first column (those with numbers) and checking second column (those with alphabet). from the above sample data, should highlight "1 | c" in sheet2 as missing. somehow, ignore '3 | a' and do nothing.

thanks for helping.

### Excel Facts

Best way to learn Power Query?
Read M is for (Data) Monkey book by Ken Puls and Miguel Escobar. It is the complete guide to Power Query.

#### GlennUK

##### Well-known Member
Can you explain how "1 | c" is missing, but "3 | a" should be ignored? They are both in Sheet2 but not in Sheet1, so what is the logical rule that is applied.

#### alvbnp

##### Board Regular
"1 | c" is considered missing because 1 is in both sheets, so we check the second column.

'3 | a" is being ignored because 3 is not in sheet1, so we can skip checking.

Btw, I forgot to mention that the real data is not sorted, not like the sample data I posted which is ascending sorted.

#### jindon

##### MrExcel MVP
try
Code:
``````Sub test()
Dim dic As Object, r As Range
Set dic = CreateObject("Scripting.Dictionary")
With Sheets("Sheet1")
For Each r In .Range("a1",.Range("a" & Rows.Count).End(xlUp))
If Not IsEmpty(r) Then
If Not dic.Exists(r.Value) Then
End If
End If
Next
End With
With Sheets("Sheet2")
.Columns("a").Interior.ColorIndex = xlNone
For Each r In .Range("a1",.Range("a" & Rows.Count).End(xlUp))
If Not IsEmpty(r) Then
If dic.exists(r.Value) Then
If Not dic.exists(r.value & r.Offset(,1).value) Then
r.Interior.Color = vbRed
End If
End If
End If
Next
End With
Set dic = Nothing
End Sub``````

#### alvbnp

##### Board Regular

Run-time error '450':
Wrong number of arguments or invalid property assignment
Code:
``dic.Add r.Value & r.Offset(, 1).Value``

try again

#### alvbnp

##### Board Regular

Using my sample data as in the first post, it wrongly highlighted:
1 | b
1 | c
2 | b

It suppose to highlight only "1 | c"

Besides that, can we make it highlighting the whole row?

#### jindon

##### MrExcel MVP
try this one
Code:
``````Sub test()
Dim dic As Object, r As Range
Set dic = CreateObject("Scripting.Dictionary")
With Sheets("Sheet1")
For Each r In .Range("a1",.Range("a" & Rows.Count).End(xlUp))
If Not IsEmpty(r) Then
If Not dic.Exists(r.Value) Then
Else
If Not dic.exists(r.Value & r.Offset(,1).Value) Then
End If
End If
End If
Next
End With
With Sheets("Sheet2")
.Cells.Interior.ColorIndex = xlNone
For Each r In .Range("a1",.Range("a" & Rows.Count).End(xlUp))
If Not IsEmpty(r) Then
If dic.exists(r.Value) Then
If Not dic.exists(r.value & r.Offset(,1).value) Then
r.EntireRow.Color = vbRed
End If
End If
End If
Next
End With
Set dic = Nothing
End Sub``````

#### alvbnp

##### Board Regular
Thanks. Working great.

Replies
3
Views
51
Replies
5
Views
97
Replies
3
Views
82
Replies
2
Views
175
Replies
1
Views
63