Analyze 2 worksheets and highlight missing values

[Livio]

I have 2 Worksheets ("Old" , "New" ).

I want to compare two columns of different worksheet

I want to highlight the cells on the column A of worksheet "New" the missing values from the column A of Worksheet "Old"

I am facing the following code:

Dim lastRow As Integer
Dim rng As Range
lastRow = Sheets("New").Range("A2:A10000").End(xlUp).Row

For i = 1 To lastRow
Set rng = Sheets("New").Range("A:A").Find(Sheets("Old").Cells(i, 1))
If rng Is Nothing Then
Sheets("New").Cells(i, 1).Interior.Color = RGB(250,0,0)
End If
Next

But is not working

 

[Ezguy4u]

This should get the ball rolling. Usually the A students will weigh in and do the same program with just 2 lines. Now notice I commented out your lines and put some new lines in. If you have any questions let us know.

Sub Commpare1()

Dim lastRow As Integer
'Dim rng As Range
'lastRow = Sheets("New").Range("A2:A10").End(xlUp).Row
lastRow = Sheets("New").Cells(Rows.Count, 1).End(xlUp).Row

For i = 1 To lastRow

'Set rng = Sheets("New").Range("A:A").Find(Sheets("Old").Cells(i, 1))
'If rng Is Nothing Then
If Sheets("Old").Range("A" & i) = "" Then

Sheets("New").Cells(i, 1).Interior.Color = RGB(250, 0, 0)
End If
Next

End Sub

20-10-09 compare.xlsm
	A
1	1
2	2
3	3
4	4
5	5
New

20-10-09 compare.xlsm
	A
1	1
2
3	3
4
5	5
Old

 

[mumps]

Try:

Sub MatchData()
    Application.ScreenUpdating = False
    Dim desWS As Worksheet, srcWS As Worksheet, arr1 As Variant, arr2 As Variant, Val As String, dic As Object
    Set srcWS = Sheets("Old")
    Set desWS = Sheets("New")
    Dim LastRow As Long
    arr1 = srcWS.Range("A2", srcWS.Range("A" & Rows.Count).End(xlUp)).Value
    arr2 = desWS.Range("A2", desWS.Range("A" & Rows.Count).End(xlUp)).Value
    Set dic = CreateObject("Scripting.Dictionary")
    For I = 1 To UBound(arr1, 1)
        Val = arr1(I, 1)
        If Not dic.Exists(Val) Then
            dic.Add Key:=Val, Item:=I + 1
        End If
    Next I
    For I = 1 To UBound(arr2, 1)
        Val = arr2(I, 1)
        If Not dic.Exists(Val) Then
            desWS.Range("A" & I + 1).Interior.Color = RGB(250, 0, 0)
        End If
    Next I
    Application.ScreenUpdating = True
End Sub

 

[ER_Neha]

Hello, I think I am getting your query either correctly or might be I understood it wrong. Please provide a feedback, If the solutions served your purpose.
Try This:

Sub macro1()
Dim i As Long
Dim j As Long
 Dim nw As Worksheet
 Dim od As Worksheet
 Set od = Sheets("Old")
 Set nw = Sheets("New")
 
 For i = 1 To nw.Cells(Rows.Count, 1).End(xlUp).Row
    For j = 1 To od.Cells(Rows.Count, 1).End(xlUp).Row
        If nw.Range("a" & i) = od.Range("a" & j) Then
            nw.Range("a" & i).EntireRow.Interior.Color = VBA.vbWhite
            Exit For
        Else
            nw.Range("a" & i).EntireRow.Interior.Color = VBA.vbYellow
        End If
    Next j
Next i
End Sub

 

[Livio]

Hello Er-Neha and mumps

Er-Neha, thank you for your availability in to try to help me. But the code from mumps was more suitable for my case.
Thank you very much for your help.

Mumps your code is working perfectly .

Thank you.

Livio

 

[mumps]

You are very welcome.