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)
<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:
<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
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