Compare 2 sheets of a column and highlight unmatched excel vba

[Sugibala]

Hi can any of u help me in resolving. i am a newbie and trying to bring the output but no use.


i want to compare column data between 2 sheets. sheet1 name is user , sheet2 name is reference
User sheet has 10 columns. I want to compare the data on column E with Sheet2 on column A


values in column E =>90000
values in column A =>10 to 15


i want to compare & highlight the data that are not in sheet1 column E with that of sheet2 column A


Eg: sheet 1 name :user
Column E values : xxxx,yyy,zz,aaa,bb,bbb,xx


sheet 2 name : reference
Column A values : xxxx,yyy,zz,aaa,bb,cc,dd
Expected output : column cell value bbb &xx to be highlighted in red since it is not matches with my sheet2.


sample code i tried on surfing but i donot know how to proceed. kindly help.i am struggling for past 2 days

Dim mycell, picklist_cell As Range
 Dim lRow2, lRow1 As Long
Dim mydiffs As Integer
Dim shtSheet1, shtSheet2 As String
Dim c As Integer

 shtSheet1 = "User"

 shtSheet2 = "Reference"

lRow2 = Sheets("Reference").Range("A2").End(xlDown).row
'Sheets("Service User").Select
'lRow1 = Range("A1").End(xlDown).row
 
'For each cell in sheet2 that is not the same in Sheet1, color it yellow
For Each mycell In ActiveWorkbook.Worksheets(shtSheet1).Range("E3:E" & LR)
 
'For Each picklist_cell In ActiveWorkbook.Worksheets(shtSheet2).Range("A2:A" & lRow2)
Do Until c = ActiveWorkbook.Worksheets(shtSheet2).Range("A2:A" & lRow2)

'If mycell.Value <> ActiveWorkbook.Worksheets(shtSheet2).Range("A2:A" & lRow2) Then
If mycell.Value <> picklist_cell.Value Then
mydiffs = mydiffs + 1
c = c + 1
End If
'Next picklist_cell


 mycell.Interior.Color = vbRed
Next mycell

[arnelgp]

you only need to add Conditional format to "E" range.

=COUNTIF(Sheet2!$A$1:$A$7,$E1)<1

see this demo. on the ribbon->home->Conditional Format->Manage.
https://www.dropbox.com/s/kiwjdesi2r...rmat.xlsx?dl=0

[Sugibala]

Hi Arnel,

Thank you very much for replying & for the effort taken to help me, i was able to get the output. But when i try to copy& paste data into the cell" E", it is not identifying the changes & not highlighting the difference/incorrect values. On typing, it works. can you suggest, also my column length will be varying .Is there any way i can define dynamic column data too?

[arnelgp]

you may try this, paste this on a Module:

Public Sub mySub()
Dim i As Integer
Dim rng As Range
With Sheet1
    For i = 1 To .Range("a1").SpecialCells(xlCellTypeLastCell).Row
        Set rng = .Range("e" & i)
        If WorksheetFunction.CountIf(Sheet2.Range("a:a"), rng) = 0 Then
            rng.Interior.Color = vbRed
        End If
    Next
End With
End Sub

on Sheet1, add this code:

Private Sub Worksheet_Change(ByVal Target As Range)
Call mySub
End Sub

[Sugibala]

Wow!!!Great!!! I got the output what i expect.. its just of 4 lines of code.. i was trying for days with long code..nothing went good.seriously, you are a legend. Thank you so so so much. Really you had helped me a lot..Thank you once again...