Macro comparing 2 columns in 2 sheets

[alvbnp]

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.

 

[GlennUK]

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]

"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]

try

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
                dic.add r.Value, Nothing
                dic.add r.Value & r.Offset(,1).Value, Nothing
            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]

Run-time error '450':
Wrong number of arguments or invalid property assignment

dic.Add r.Value & r.Offset(, 1).Value

 

[jindon]

try again

 

[alvbnp]

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]

try this one

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
                dic.add r.Value, Nothing
                dic.add r.Value & r.Offset(,1).Value, Nothing
            Else
                If Not dic.exists(r.Value & r.Offset(,1).Value) Then
                    dec.add r.Value & r.Offset(,1).Value
                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]

Thanks. Working great.