What's New
Q & A
Tip Jar
C# Helper...
Follow VBHelper on Twitter
MSDN Visual Basic Community
TitleUse VBA to compare two lists in Excel and highlight the cells in the second that are not in the first
DescriptionThis example shows how to use VBA to compare two lists in Excel and highlight the cells in the second that are not in the first.
KeywordsExcel, VBA, compare lists, compare, lists
CategoriesOffice, Utilities
Sheet1 and Sheet2 contain two column lists. The code highlghts entries in Sheet2 that do not appear in Sheet1.

The code loops through the entries on Sheet 2. For each entry, it loops throuh Sheet1's list looking for it. If the code doesn't find the entry, it highlights the entry on Sheet2.

Private Sub cmdCompare_Click()
Dim first_index As Integer
Dim last_index As Integer
Dim sheet1 As Worksheet
Dim sheet2 As Worksheet
Dim r1 As Integer
Dim r2 As Integer
Dim found As Boolean
Dim rng As Range

    Set sheet1 = Worksheets(1)
    Set sheet2 = Worksheets(2)

    ' Set first_index and last_index somehow. If you know
    ' what they are, just enter them. Otherwise search the
    ' cells or use UsedRange or something.
    first_index = 1
    last_index = 5

    ' For each entry in the second worksheet, see if it's
    ' in the first.
    For r2 = first_index To last_index
        found = False
        ' See if the r1-th entry on sheet 2 is in the sheet
        ' 1 list.
        For r1 = first_index To last_index
            If sheet1.Cells(r1, 1) = sheet2.Cells(r2, 1) _
                And _
               sheet1.Cells(r1, 2) = sheet2.Cells(r2, 2) _
                ' We found a match.
                found = True
                Exit For
            End If
        Next r1

        ' See if we found it.
        If Not found Then
            ' Flag this cell.
            sheet2.Cells(r2, 1).Interior.ColorIndex = 35
            sheet2.Cells(r2, 2).Interior.ColorIndex = 35
        End If
    Next r2
End Sub
See also Use VBA to compare two Ranges in Excel and highlight the cells that are different.
Copyright © 1997-2010 Rocky Mountain Computer Consulting, Inc.   All rights reserved.