Consulting

Results 1 to 5 of 5

Thread: Compare 2 sheets of a column and highlight unmatched excel vba

  1. #1
    VBAX Regular
    Joined
    Aug 2021
    Posts
    11
    Location

    Compare 2 sheets of a column and highlight unmatched excel vba

    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

  2. #2
    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

  3. #3
    VBAX Regular
    Joined
    Aug 2021
    Posts
    11
    Location

    Red face

    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?

  4. #4
    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

  5. #5
    VBAX Regular
    Joined
    Aug 2021
    Posts
    11
    Location
    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...

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •