Spreadsheet: 93658d1293332550-excel-2007-highlight-matching-pairs-excel-2007-test_for_duplicate_pairs-2-
Sheet: Sheet1

 | [NAME] |  | Potatoes | Oranges |  | Room 1 | Cabbages | Grapefruit
 | [NAME] |  | Carrots | Apples |  | Room 2 | Swedes | Pears
 | [NAME] |  | Cabbages | Lemons |  | Room 3 | Parsnips | Bananas
 | [NAME] |  | Swedes |  |  | Room 4 | Onions | Apples |  |  | Pairs are columns D and E another pair columns H and I
 | [NAME] |  | Parsnips | Oranges |  | Room 5 | [NAME] | Coconuts
 | [NAME] |  | Onions | Apples |  | Room 6 | Potatoes |  |  |  | I want to highlight, using conditional format, any pairs (D and E) that
 | [NAME] |  | [NAME] | Lemons |  | Room 7 | Carrots | Pears |  |  | correspond with any pairs from (H and I).
 | [NAME] |  | Potatoes | Pears |  | Room 8 | Cabbages | Bananas
 | [NAME] |  | Carrots |  |  | Cage 1 | Swedes | Apples |  |  | Only highlight D and E.
 | [NAME] |  | Cabbages | Apples |  | Cage 2 | Parsnips | Coconuts
 | [NAME] |  | Swedes | Lemons |  | Cage 3 | Onions | Grapefruit |  |  | As Example....H14 (Carrots) and I14 (Apples) is a pair     (Oh dear I didn't want to say that....[NAME])
 | [NAME] |  | Parsnips | Pears |  | Cage 4 | [NAME] | Pears
 | [NAME] |  |  | Oranges |  | Cage 5 |  | Bananas |  |  | The example therefor should highlight both pairs (D2-E2) and (D30-E30) plus any other matches....
 | [NAME] |  | [NAME] | Apples |  | Cage 6 | Carrots | Apples
 | [NAME] |  | Potatoes | Lemons |  | Cage 7 | Cabbages | Coconuts |  |  | Columns D and E change on a daily basis, columns H and I change on a weekly basis (usually but not always so)
 | [NAME] |  | Carrots | Pears |  | Cage 8 | Swedes | Grapefruit
 | [NAME] |  | Cabbages | Oranges |  | Cage 9 | Parsnips | Pears |  |  | Pairs columns D and E can and usually are duplicated.
 | [NAME] |  | Swedes | Apples |  | Cage 10 | Onions
 | [NAME] 9 |  | Parsnips | Lemons |  | Cage 11 | [NAME] | Apples |  |  | Pairs column H and I are very rarely duplicated (even though I may have done so in the example)
 | [NAME] 10 |  | Onions | Pears |  | Pit 1 | Potatoes | Coconuts
 | Store 1 |  | [NAME] | Oranges |  | Pit 2 | Carrots | Grapefruit |  |  | Columns B and G can be ignored.
 | Store 2 |  | Potatoes | Apples |  | Pit 3 | Cabbages | Pears
 | Store 3 |  |  | Lemons |  | Pit 4 | Swedes | Bananas |  |  | Columns D and E can run to over 600+
 | Store 4 |  | Cabbages | Pears |  | Pit 5 | Parsnips | Apples
 | Store 5 |  | Swedes | Oranges |  | Pit 6 | Onions | Coconuts |  |  | Columns H and I usually about 200
 | Store 6 |  | Parsnips | Apples |  | Pit 7 | [NAME] | Grapefruit
 | Store 7 |  | Onions | Lemons |  | Pit 8 | Potatoes | Pears |  |  | The entries are total fabrication in an attempt to simplify
 | Store 8 |  | [NAME] | Pears |  | Pit 9 | Carrots | Bananas
 | Store 9 |  | Potatoes | Oranges |  | Pit 10 | Cabbages | Apples |  |  | I have a working format but had to point to every pair in H and I to do it
 | Store 10 |  | Carrots | Apples |  | Pit 11 | Swedes | Coconuts |  |  | It worked out at over 40 seperate conditions with ten pointers in every one, did my head in and is a pig to check for mistakes.
 |  |  |  |  |  |  |  |  |  |  | I know there must be an easier way of doing it but I don't know how.