Home
Search
 
What's New
Index
Books
Links
Q & A
Newsletter
Banners
 
Feedback
Tip Jar
 
C# Helper...
 
XML RSS Feed
Follow VBHelper on Twitter
 
 
 
MSDN Visual Basic Community
 
 
 
 
 
TitleUse VBA to compare two Ranges in Excel and highlight the cells that are different
DescriptionThis example shows how to use VBA to compare two Ranges in Excel and highlight the cells that are different.
KeywordsExcel, Range, compare
CategoriesOffice, Utilities
 
The MakeTestData subroutine generates two sets of data. It loops over the cells in a rectangular area, setting their values. It randomly makes some negative so they two areas are not exactly the same. Probably the most interesting part of this routine is in the final two lines, which create named regions for the areas.
 
Sub MakeTestData()
Dim active_sheet As Worksheet
Dim r As Integer
Dim c As Integer

    Randomize
    For r = 1 To 10
        For c = 1 To 7
            Cells(r, c).Formula = r * 100 + c
            If Int(Rnd * 8) < 1 Then
                ' Make them different.
                Cells(r, c + 10).Formula = -(r * 100 + c)
            Else
                ' Make them match.
                Cells(r, c + 10).Formula = r * 100 + c
            End If
        Next c
    Next r

    Set active_sheet = ActiveSheet
    active_sheet.Names.Add Name:="Range1", _
        RefersToR1C1:="=R1C1:R10C7"
    active_sheet.Names.Add Name:="Range2", _
        RefersToR1C1:="=R1C11:R10C17"
End Sub
 
Subroutine MarkDifferences highlights the cells that are different in the two regions. For testing, the names of the regions are hard-coded but commented code taks the names of the regions from the user.

Excel regions do not need to be contiguous and they can be selected in several orders (e.g. upper-left corner first, lower-right corner first, and so forth). In general, that makes it hard to match cells in two regions exactly. To work around this, the program builds collections holding each region's cells indexed by their row and column indexes relative to the region's starting cell. This doesn't completely eliminate the issue because the regions must have the same starting cell but it helps.

Next the code loops through the first region's collection, looking for corresponding cells in the second collection. If it finds a corresponding cell, it compares the cels' values and highlights the first cell if they are different. If there is no corresponding cell in the second collection, the code highlights the unmatched first cell.

The code repeats this step to compare the cells in the second collection to those in the first.

 
Sub MarkDifferences()
Dim active_sheet As Worksheet
Dim name1 As String
Dim name2 As String
Dim range1 As Range
Dim range2 As Range
Dim cells1 As Collection
Dim cells2 As Collection
Dim cell1 As Range
Dim cell2 As Range
Dim key As String
Dim no_match As Boolean

    Set active_sheet = ActiveSheet
'    name1 = InputBox$("First Range Name:", "First Range",
' "")
name1 = "Range1"
    If Len(name1) = 0 Then Exit Sub
    Set range1 = active_sheet.Range(name1)

'    name2 = InputBox$("Second Range Name:", "Second
' Range", "")
name2 = "Range2"
    If Len(name2) = 0 Then Exit Sub
    Set range2 = active_sheet.Range(name2)

    ' Make normal collections holding the cells.
    Set cells1 = New Collection
    For Each cell1 In range1.Cells
        key = cell1.Row - range1.Row & "," & cell1.Column - _
            range1.Column
        cells1.Add cell1, key
    Next cell1

    Set cells2 = New Collection
    For Each cell2 In range2.Cells
        key = cell2.Row - range2.Row & "," & cell2.Column - _
            range2.Column
        cells2.Add cell2, key
    Next cell2

    ' Examine the cells in the first collection.
    For Each cell1 In cells1
        On Error Resume Next
        Err.Clear
        key = cell1.Row - range1.Row & "," & cell1.Column - _
            range1.Column
        Set cell2 = cells2(key)
        If Err.Number <> 0 Then
            ' The second cell is missing.
            no_match = True
        ElseIf cell1.Text <> cell2.Text Then
            ' The cells don't match.
            no_match = True
        Else
            no_match = False
        End If

        ' If the cells don't match, color cell1.
        If no_match Then
            With cell1.Interior
                .ColorIndex = 35
                .Pattern = xlSolid
            End With
        Else
            With cell1.Interior
                .ColorIndex = xlNone
            End With
        End If
    Next cell1

    ' Examine the cells in the second collection.
    For Each cell2 In cells2
        On Error Resume Next
        Err.Clear
        key = cell2.Row - range2.Row & "," & cell2.Column - _
            range2.Column
        Set cell1 = cells1(key)
        If Err.Number <> 0 Then
            ' The second cell is missing.
            no_match = True
        ElseIf cell2.Text <> cell1.Text Then
            ' The cells don't match.
            no_match = True
        Else
            no_match = False
        End If

        ' If the cells don't match, color cell2.
        If no_match Then
            With cell2.Interior
                .ColorIndex = 35
                .Pattern = xlSolid
            End With
        Else
            With cell2.Interior
                .ColorIndex = xlNone
            End With
        End If
    Next cell2
End Sub
 
Subroutine ListRange is a debugging tool that lists the cells in a range named Range4. To create an interesting region, control-click-and-drag several times to select multiple non-contiguous areas. In the toolbar's Name box, enter the name you want to give the selected region (Region4).
 
Sub ListRange()
Dim range4 As Range
Dim cell As Range

    Set range4 = ActiveSheet.Range("Range4")
    Debug.Print "Range: (" & range4.Row & ", " & _
        range4.Column & ")"
    For Each cell In range4.Cells
        Debug.Print "(" & cell.Row & ", " & cell.Column & _
            ")"
    Next cell
End Sub
 
Note: In general, you should not allow macros to run in a strange Office file such as the Excel file included in this example. When you open the file and Excel asks whether you want to enable macros, click Disable Macros. Use the Tools\Macro\Visual Basic Editor menu command to open the Visual Basic editor and examine the VBA code to make sure there's nothing harmful in it. If everything is safe, you can close and reopen the file enabling macros.
 
 
Copyright © 1997-2010 Rocky Mountain Computer Consulting, Inc.   All rights reserved.
  Updated