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

[Eazyeagle]

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)

Range A		Range B
1	-	2		7	-	15		16	-	X1		1	-	2
1	-	3		8	-	X1		17	-	X1		2	-	4
2	-	4		9	-	10		18	-	X1		4	-	6
2	-	5		9	-	11		19	-	X1		6	-	8
3	-	4		10	-	X1		20	-	22		8	-	X1
3	-	5		11	-	12		20	-	23		X1	-	X2
4	-	6		11	-	13		21	-	X3		X2	-	21
4	-	7		12	-	X1		22	-	24		21	-	X3
5	-	6		13	-	X1		22	-	25		X3	-	44
5	-	7		14	-	16						44	-	46
6	-	8		14	-	17						46	-	49
6	-	9		15	-	18						49	-	50
7	-	14		15	-	19						50	-	52
												52	-	66
												66	-	70
												70	-	E1
												E	-	999

<colgroup><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col></colgroup><tbody>
</tbody>

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


Like this:

Range A		Range B
<strike>1</strike>	<strike>-</strike>	<strike>2</strike>		7	-	15		16	-	X1		<strike>1</strike>	<strike>-</strike>	<strike>2</strike>
1	-	3		<strike>8</strike>	<strike>-</strike>	<strike>X1</strike>		17	-	X1		<strike>2</strike>	<strike>-</strike>	<strike>4</strike>
<strike>2</strike>	<strike>-</strike>	<strike>4</strike>		9	-	10		18	-	X1		<strike>4</strike>	<strike>-</strike>	<strike>6</strike>
2	-	5		9	-	11		19	-	X1		<strike>6</strike>	<strike>-</strike>	<strike>8</strike>
3	-	4		10	-	X1		20	-	22		<strike>8</strike>	<strike>-</strike>	<strike>X1</strike>
3	-	5		11	-	12		20	-	23		X1	-	X2
<strike>4</strike>	<strike>-</strike>	<strike>6</strike>		11	-	13		21	-	X3		X2	-	21
4	-	7		12	-	X1		22	-	24		21	-	X3
5	-	6		13	-	X1		22	-	25		X3	-	44
5	-	7		14	-	16						44	-	46
<strike>6</strike>	<strike>-</strike>	<strike>8</strike>		14	-	17						46	-	49
6	-	9		15	-	18						49	-	50
7	-	14		15	-	19						50	-	52
												52	-	66
												66	-	70
												70	-	E1
												E	-	999

<colgroup><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col></colgroup><tbody>
</tbody>

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

 

[Fluff]

You're welcome & thanks for the feedback