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

What is the fastest way to copy a formula?
If A2:A50000 contain data. Enter a formula in B2. Select B2. Double-click the Fill Handle and Excel will shoot the formula down to B50000.
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,908
Messages
6,175,304
Members
452,633
Latest member
DougMo

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