|Title||Use VBA to compare two lists in Excel and highlight the cells in the second that are not in the first|
|Description||This 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.|
|Keywords||Excel, VBA, compare lists, compare, lists|
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) _
sheet1.Cells(r1, 2) = sheet2.Cells(r2, 2) _
' We found a match.
found = True
' 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
See also Use VBA to compare two Ranges in Excel and highlight the cells that are different.