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

What is the shortcut key for Format Selection?
Ctrl+1 (the number one) will open the Format dialog for whatever is selected.
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

D
  • Solved
finding changes and looking back
2
Replies
10
Views
411
felixstraube
F
J
  • Question Question
Average of Two Columns Meeting a Specific Criterion
Replies
4
Views
199
JeffFinnan
J
R
  • Question Question
Highlighting cells based on criteria
Replies
0
Views
418
rahildhody
R
A
  • Solved
Lookup PAAF ID based on Worker ID and date range
Replies
3
Views
123
Fluff
Fluff
Asbestos_Jen
  • Question Question
Compare multiple parameters of samples to reference values (Excel 2007)
Replies
3
Views
359
Asbestos_Jen
Asbestos_Jen

Forum statistics

Threads
1,223,894
Messages
6,175,252
Members
452,623
Latest member
Techenthusiast

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