You can do this with a combination of conditional formatting and a user defined function.
User defined function
Function MatchAndDuplicate(MyBigRange As Range, MySmallRange As Range) As Long
Dim N As Long
If MySmallRange(1) = MySmallRange(2) Then
For N = 1 To MyBigRange.Rows.Count
If MyBigRange(N, 1) = MySmallRange(1) And MyBigRange(N, 2) = MySmallRange(2) Then
MatchAndDuplicate = MatchAndDuplicate + 1
End If
Next N
End If
End Function
Open the VBA editor by hitting Alt F11.
Insert a new module with Insert - Module
Paste in the above function
Go back to the sheet by hitting Alt F11.
Now set up the conditional formatting. I've assumed columns A and B are used and rows 1 to 7 are used.
Create a new rule and specify that it is determined with a formula.
Use the formula
=MatchAndDuplicate($A$1:$B$7,$A1:$B1)>1
and apply this to
=$A$1:$B$7
Remember to save the workbook as a macro enabled workbook .xlsm
An example is attached.
Bookmarks