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
 

Excel Facts

Which Excel functions can ignore hidden rows?
The SUBTOTAL and AGGREGATE functions ignore hidden rows. AGGREGATE can also exclude error cells and more.
Sort by date Sort by votes
Hi & welcome to MrExcel.
replace these two lines
Code: 
cell1.Interior.ColorIndex = 45
                    cell2.Interior.ColorIndex = 45
with
Code: 
   cell1.Font.color = vbRed
   cell1.Font.Strikethrough = True
   cell2.Font.color = vbRed
   cell2.Font.Strikethrough = True
 
Upvote 0
Fluff said:
Hi & welcome to MrExcel.
replace these two lines
Code: 
cell1.Interior.ColorIndex = 45
                    cell2.Interior.ColorIndex = 45
with
Code: 
   cell1.Font.color = vbRed
   cell1.Font.Strikethrough = True
   cell2.Font.color = vbRed
   cell2.Font.Strikethrough = True
Click to expand...

That's a stap ehead!
I'm pleased but...
The result is now...

Like this:
[TABLE="class: cms_table, width: 410"]
<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]<strike style="color: rgb(255, 0, 0);">X1</strike>[/TD]
[TD][/TD]
[TD]<strike>1</strike>[/TD]
[TD]<strike>-</strike>[/TD]
[TD]<strike>2</strike>[/TD]
[/TR]
[TR]
[TD]<strike style="color: rgb(255, 0, 0);">1</strike>[/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]<strike style="color: rgb(255, 0, 0);">X1</strike>[/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]<strike style="color: rgb(255, 0, 0);">X1</strike>[/TD]
[TD][/TD]
[TD]<strike>4</strike>[/TD]
[TD]<strike>-</strike>[/TD]
[TD]<strike>6</strike>[/TD]
[/TR]
[TR]
[TD]<strike style="color: rgb(255, 0, 0);">2</strike>[/TD]
[TD]-[/TD]
[TD]5[/TD]
[TD][/TD]
[TD]9[/TD]
[TD]-[/TD]
[TD]11[/TD]
[TD][/TD]
[TD]19[/TD]
[TD]-[/TD]
[TD]<strike style="color: rgb(255, 0, 0);">X1</strike>[/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]<strike style="color: rgb(255, 0, 0);">4</strike>[/TD]
[TD][/TD]
[TD]10[/TD]
[TD]-[/TD]
[TD]<strike style="color: rgb(255, 0, 0);">X1</strike>[/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]<strike style="color: rgb(255, 0, 0);">X1</strike>[/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]<strike style="color: rgb(255, 0, 0);">4</strike>[/TD]
[TD]-[/TD]
[TD]7[/TD]
[TD][/TD]
[TD]12[/TD]
[TD]-[/TD]
[TD]<strike style="color: rgb(255, 0, 0);">X1</strike>[/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]<strike style="color: rgb(255, 0, 0);">6</strike>[/TD]
[TD][/TD]
[TD]13[/TD]
[TD]-[/TD]
[TD]<strike style="color: rgb(255, 0, 0);">X1</strike>[/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]<strike style="color: rgb(255, 0, 0);">6</strike>[/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]


That's not quite what I like.. We are close... But...
I'd like to see the exact matches between range A and range B...

So if in range B
1-2 is marked,
than mark in range A only 1-2!
and not 1- as well!!!

Thank you in advance...
 
Upvote 0
Thank you Fluff,

That works as expected ok!
Just one question... It marks all numbers that are equal in both ranges.
But I'm looking for the exact matching combinations within the ranges.

For example if in range B 1-2 is added, than I'm looking for the exact match in range A => 1-2
And not the half match 1- or -2

Any idea?
 
Upvote 0
If the code you posted doesn't work, why did you say
It works. But I still have to strike through all the identical data sets manually
Click to expand...
?
 
Last edited:
Upvote 0
Because I'm happy that part of it works... What I coudn't work out before.
(I'm sorry for my Enlish and I'm just starting to work with VBA...) ;)
 
Upvote 0
Will the RangeB always be one area as shown?
 
Upvote 0
It will always be as shown.
The range B can be in a different set of collumns, but it will be as shown.

At this moment I'm working with a B range that is situated in the collumns DS to DU.
And the next B range will be in the collumns EB to ED, etc.
 
Upvote 0
Try
Code: 
Sub Eazyeagle()
   Dim Ar As Areas
   Dim Cl As Range
   Dim Valu As String
   Dim i As Long
   
   Set Ar = Selection.Areas
   If Ar.Count <= 1 Then
      MsgBox "Please select more than one area."
      Exit Sub
   End If
   With Cells
      .Borders.LineStyle = xlNone
      .Font.color = vbBlack
      .Font.Strikethrough = False
   End With
   
   With CreateObject("scripting.dictionary")
      For Each Cl In Ar(Ar.Count).Columns(1).Cells
         Valu = join(Application.Index(Cl.Resize(, 3).Value, 1, 0), "|")
         .Item(Valu) = Empty
      Next Cl
      For i = 1 To Ar.Count - 1
         Ar(i).BorderAround , xlThin, xlColorIndexAutomatic
         For Each Cl In Ar(i).Columns(1).Cells
            Valu = join(Application.Index(Cl.Resize(, 3).Value, 1, 0), "|")
            If .Exists(Valu) Then
               Cl.Resize(, 3).Font.color = vbRed
               Cl.Resize(, 3).Font.Strikethrough = True
            End If
         Next Cl
      Next i
   End With
End Sub
 
Upvote 0
This works perfect.
I've to change my collumns on the A range to one collumnset of 3collumns in stead of 4 collumns sets of 3collumns. But it works amazing.
Thank you very much!

Eazyeagle
 
Upvote 0
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,819
Messages
6,181,153
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