Compare Ranges In Excel VBA (In Easy Steps)

Trang chủ » Visual Basic Excel Compare Cell Values » Compare Ranges In Excel VBA (In Easy Steps)

Có thể bạn quan tâm

Below we will look at a program in Excel VBA that compares randomly selected ranges and highlights cells that are unique. If you are not familiar with areas yet, we highly recommend you to read this page first.

Situation:

Compare Ranges in Excel VBA

Note: the only unique value in this example is the 3 since all other values occur in at least one more area. To select Range("B2:B7,D3:E6,D8:E9"), hold down Ctrl and select each area.

Place a command button on your worksheet and add the following code lines:

1. First, we declare four Range objects and two variables of type Integer.

Dim rangeToUse As Range, singleArea As Range, cell1 As Range, cell2 As Range, i As Integer, j As Integer

2. We initialize the Range object rangeToUse with the selected range.

Set rangeToUse = Selection

3. Add the line which changes the background color of all cells to 'No Fill'. Also add the line which removes the borders of all cells.

Cells.Interior.ColorIndex = 0 Cells.Borders.LineStyle = xlNone

4. Inform the user when he or she only selects one area.

If Selection.Areas.Count <= 1 Then MsgBox "Please select more than one area." Else End If

The next code lines (at 5, 6 and 7) must be added between Else and End If.

5. Color the cells of the selected areas.

rangeToUse.Interior.ColorIndex = 38

6. Border each area.

For Each singleArea In rangeToUse.Areas singleArea.BorderAround ColorIndex:=1, Weight:=xlThin Next singleArea

7. The rest of this program looks as follows.

For i = 1 To rangeToUse.Areas.Count For j = i + 1 To rangeToUse.Areas.Count For Each cell1 In rangeToUse.Areas(i) For Each cell2 In rangeToUse.Areas(j) If cell1.Value = cell2.Value Then cell1.Interior.ColorIndex = 0 cell2.Interior.ColorIndex = 0 End If Next cell2 Next cell1 Next j Next i

Explanation: this may look a bit overwhelming, but it's not that difficult. rangeToUse.Areas.Count equals 3, so the first two code lines reduce to For i = 1 to 3 and For j = i + 1 to 3. For i = 1, j = 2, Excel VBA compares all values of the first area with all values of the second area. For i = 1, j = 3, Excel VBA compares all values of the first area with all values of the third area. For i = 2, j = 3, Excel VBA compares all values of the second area with all values of the third area. If values are the same, it sets the background color of both cells to 'No Fill', because they are not unique.

Result when you click the command button on the sheet:

Compare Ranges Result

Từ khóa » Visual Basic Excel Compare Cell Values