VBA compare two ranges of datasets on same worksheet and mark identical sets red and strike through

E

Eazyeagle

New Member
Joined
Jan 31, 2019
Messages
6
Hi there,


I'm new here and hope that any of you will be able to help me out.
I have a form that has a set of values that is kind of fixed (the range A in the table below) and a range that is dynamic (the range B in the table below(not always in the same collumns)




[TABLE="width: 410"]
<colgroup><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col></colgroup><tbody>[TR]
[TD="colspan: 11"]Range A[/TD]
[TD][/TD]
[TD="colspan: 3"]Range B[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]-[/TD]
[TD]2[/TD]
[TD] [/TD]
[TD]7[/TD]
[TD]-[/TD]
[TD]15[/TD]
[TD] [/TD]
[TD]16[/TD]
[TD]-[/TD]
[TD]X1[/TD]
[TD][/TD]
[TD]1[/TD]
[TD]-[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]-[/TD]
[TD]3[/TD]
[TD][/TD]
[TD]8[/TD]
[TD]-[/TD]
[TD]X1[/TD]
[TD][/TD]
[TD]17[/TD]
[TD]-[/TD]
[TD]X1[/TD]
[TD][/TD]
[TD]2[/TD]
[TD]-[/TD]
[TD]4[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]-[/TD]
[TD]4[/TD]
[TD][/TD]
[TD]9[/TD]
[TD]-[/TD]
[TD]10[/TD]
[TD][/TD]
[TD]18[/TD]
[TD]-[/TD]
[TD]X1[/TD]
[TD][/TD]
[TD]4[/TD]
[TD]-[/TD]
[TD]6[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]-[/TD]
[TD]5[/TD]
[TD][/TD]
[TD]9[/TD]
[TD]-[/TD]
[TD]11[/TD]
[TD][/TD]
[TD]19[/TD]
[TD]-[/TD]
[TD]X1[/TD]
[TD][/TD]
[TD]6[/TD]
[TD]-[/TD]
[TD]8[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]-[/TD]
[TD]4[/TD]
[TD][/TD]
[TD]10[/TD]
[TD]-[/TD]
[TD]X1[/TD]
[TD][/TD]
[TD]20[/TD]
[TD]-[/TD]
[TD]22[/TD]
[TD][/TD]
[TD]8[/TD]
[TD]-[/TD]
[TD]X1[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]-[/TD]
[TD]5[/TD]
[TD][/TD]
[TD]11[/TD]
[TD]-[/TD]
[TD]12[/TD]
[TD][/TD]
[TD]20[/TD]
[TD]-[/TD]
[TD]23[/TD]
[TD][/TD]
[TD]X1[/TD]
[TD]-[/TD]
[TD]X2[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]-[/TD]
[TD]6[/TD]
[TD][/TD]
[TD]11[/TD]
[TD]-[/TD]
[TD]13[/TD]
[TD][/TD]
[TD]21[/TD]
[TD]-[/TD]
[TD]X3[/TD]
[TD][/TD]
[TD]X2[/TD]
[TD]-[/TD]
[TD]21[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]-[/TD]
[TD]7[/TD]
[TD][/TD]
[TD]12[/TD]
[TD]-[/TD]
[TD]X1[/TD]
[TD][/TD]
[TD]22[/TD]
[TD]-[/TD]
[TD]24[/TD]
[TD][/TD]
[TD]21[/TD]
[TD]-[/TD]
[TD]X3[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]-[/TD]
[TD]6[/TD]
[TD][/TD]
[TD]13[/TD]
[TD]-[/TD]
[TD]X1[/TD]
[TD][/TD]
[TD]22[/TD]
[TD]-[/TD]
[TD]25[/TD]
[TD][/TD]
[TD]X3[/TD]
[TD]-[/TD]
[TD]44[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]-[/TD]
[TD]7[/TD]
[TD][/TD]
[TD]14[/TD]
[TD]-[/TD]
[TD]16[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD] [/TD]
[TD][/TD]
[TD]44[/TD]
[TD]-[/TD]
[TD]46[/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]-[/TD]
[TD]8[/TD]
[TD][/TD]
[TD]14[/TD]
[TD]-[/TD]
[TD]17[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD] [/TD]
[TD][/TD]
[TD]46[/TD]
[TD]-[/TD]
[TD]49[/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]-[/TD]
[TD]9[/TD]
[TD][/TD]
[TD]15[/TD]
[TD]-[/TD]
[TD]18[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD] [/TD]
[TD][/TD]
[TD]49[/TD]
[TD]-[/TD]
[TD]50[/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD]-[/TD]
[TD]14[/TD]
[TD] [/TD]
[TD]15[/TD]
[TD]-[/TD]
[TD]19[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD][/TD]
[TD]50[/TD]
[TD]-[/TD]
[TD]52[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]52[/TD]
[TD]-[/TD]
[TD]66[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]66[/TD]
[TD]-[/TD]
[TD]70[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]70[/TD]
[TD]-[/TD]
[TD]E1[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]E[/TD]
[TD]-[/TD]
[TD]999[/TD]
[/TR]
</tbody>[/TABLE]


Now I'd like to have the identical datasets within each range to be red and striked through


Like this:
[TABLE="width: 410"]
<colgroup><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col></colgroup><tbody>[TR]
[TD="colspan: 11"]Range A[/TD]
[TD][/TD]
[TD="colspan: 3"]Range B[/TD]
[/TR]
[TR]
[TD]<strike>1</strike>[/TD]
[TD]<strike>-</strike>[/TD]
[TD]<strike>2</strike>[/TD]
[TD] [/TD]
[TD]7[/TD]
[TD]-[/TD]
[TD]15[/TD]
[TD] [/TD]
[TD]16[/TD]
[TD]-[/TD]
[TD]X1[/TD]
[TD][/TD]
[TD]<strike>1</strike>[/TD]
[TD]<strike>-</strike>[/TD]
[TD]<strike>2</strike>[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]-[/TD]
[TD]3[/TD]
[TD][/TD]
[TD]<strike>8</strike>[/TD]
[TD]<strike>-</strike>[/TD]
[TD]<strike>X1</strike>[/TD]
[TD][/TD]
[TD]17[/TD]
[TD]-[/TD]
[TD]X1[/TD]
[TD][/TD]
[TD]<strike>2</strike>[/TD]
[TD]<strike>-</strike>[/TD]
[TD]<strike>4</strike>[/TD]
[/TR]
[TR]
[TD]<strike>2</strike>[/TD]
[TD]<strike>-</strike>[/TD]
[TD]<strike>4</strike>[/TD]
[TD][/TD]
[TD]9[/TD]
[TD]-[/TD]
[TD]10[/TD]
[TD][/TD]
[TD]18[/TD]
[TD]-[/TD]
[TD]X1[/TD]
[TD][/TD]
[TD]<strike>4</strike>[/TD]
[TD]<strike>-</strike>[/TD]
[TD]<strike>6</strike>[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]-[/TD]
[TD]5[/TD]
[TD][/TD]
[TD]9[/TD]
[TD]-[/TD]
[TD]11[/TD]
[TD][/TD]
[TD]19[/TD]
[TD]-[/TD]
[TD]X1[/TD]
[TD][/TD]
[TD]<strike>6</strike>[/TD]
[TD]<strike>-</strike>[/TD]
[TD]<strike>8</strike>[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]-[/TD]
[TD]4[/TD]
[TD][/TD]
[TD]10[/TD]
[TD]-[/TD]
[TD]X1[/TD]
[TD][/TD]
[TD]20[/TD]
[TD]-[/TD]
[TD]22[/TD]
[TD][/TD]
[TD]<strike>8</strike>[/TD]
[TD]<strike>-</strike>[/TD]
[TD]<strike>X1</strike>[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]-[/TD]
[TD]5[/TD]
[TD][/TD]
[TD]11[/TD]
[TD]-[/TD]
[TD]12[/TD]
[TD][/TD]
[TD]20[/TD]
[TD]-[/TD]
[TD]23[/TD]
[TD][/TD]
[TD]X1[/TD]
[TD]-[/TD]
[TD]X2[/TD]
[/TR]
[TR]
[TD]<strike>4</strike>[/TD]
[TD]<strike>-</strike>[/TD]
[TD]<strike>6</strike>[/TD]
[TD][/TD]
[TD]11[/TD]
[TD]-[/TD]
[TD]13[/TD]
[TD][/TD]
[TD]21[/TD]
[TD]-[/TD]
[TD]X3[/TD]
[TD][/TD]
[TD]X2[/TD]
[TD]-[/TD]
[TD]21[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]-[/TD]
[TD]7[/TD]
[TD][/TD]
[TD]12[/TD]
[TD]-[/TD]
[TD]X1[/TD]
[TD][/TD]
[TD]22[/TD]
[TD]-[/TD]
[TD]24[/TD]
[TD][/TD]
[TD]21[/TD]
[TD]-[/TD]
[TD]X3[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]-[/TD]
[TD]6[/TD]
[TD][/TD]
[TD]13[/TD]
[TD]-[/TD]
[TD]X1[/TD]
[TD][/TD]
[TD]22[/TD]
[TD]-[/TD]
[TD]25[/TD]
[TD][/TD]
[TD]X3[/TD]
[TD]-[/TD]
[TD]44[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]-[/TD]
[TD]7[/TD]
[TD][/TD]
[TD]14[/TD]
[TD]-[/TD]
[TD]16[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD] [/TD]
[TD][/TD]
[TD]44[/TD]
[TD]-[/TD]
[TD]46[/TD]
[/TR]
[TR]
[TD]<strike>6</strike>[/TD]
[TD]<strike>-</strike>[/TD]
[TD]<strike>8</strike>[/TD]
[TD][/TD]
[TD]14[/TD]
[TD]-[/TD]
[TD]17[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD] [/TD]
[TD][/TD]
[TD]46[/TD]
[TD]-[/TD]
[TD]49[/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]-[/TD]
[TD]9[/TD]
[TD][/TD]
[TD]15[/TD]
[TD]-[/TD]
[TD]18[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD] [/TD]
[TD][/TD]
[TD]49[/TD]
[TD]-[/TD]
[TD]50[/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD]-[/TD]
[TD]14[/TD]
[TD] [/TD]
[TD]15[/TD]
[TD]-[/TD]
[TD]19[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD][/TD]
[TD]50[/TD]
[TD]-[/TD]
[TD]52[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]52[/TD]
[TD]-[/TD]
[TD]66[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]66[/TD]
[TD]-[/TD]
[TD]70[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]70[/TD]
[TD]-[/TD]
[TD]E1[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]E[/TD]
[TD]-[/TD]
[TD]999[/TD]
[/TR]
</tbody>[/TABLE]



I've this vba script;
Sub FindMatch()


'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 = 0
'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 = 45
cell2.Interior.ColorIndex = 45
End If
Next cell2
Next cell1
Next j
Next i
'From: https://www.excel-easy.com/vba/examples/compare-ranges.html


End Sub


It works. But I still have to strike through all the identical data sets manually
And I'd rather have the text to be red in stead of the complete cell to change to orange


I hope someone can help me out?!


Regards


EazyEagle
 
Sort by date Sort by votes
You're welcome & thanks for the feedback
 
Upvote 0

Excel Facts

Did you know Excel offers Filter by Selection?
Add the AutoFilter icon to the Quick Access Toolbar. Select a cell containing Apple, click AutoFilter, and you will get all rows with Apple
You must log in or register to reply here.

Similar threads

O
  • Solved
Conditional Format Compare Ranges
Replies
3
Views
35
Fluff
Fluff
H
  • Question Question
Transposing Data - Evaluating formula to an array error.
Replies
1
Views
59
Skyybot
Skyybot
D
  • Solved
finding changes and looking back
2
Replies
10
Views
420
felixstraube
F
G
  • Solved
VBA Userform Date Format
2
Replies
12
Views
304
Jack George
J
E
  • Solved
Duplicate row based on cell with text separated by semicolon
Replies
5
Views
99
JEC
JEC

Forum statistics

Threads
1,224,823
Messages
6,181,178
Members
453,021
Latest member
Justyna P

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top