Highlight row if two matches are found

[AlanB]

Hi - I wondered if you could help.
I am struggling to create a formula or VBA to solve a problem I have. I need to look down column A of Sheet looking for the numbers 1 to 6 and 14-20. There will only one version of each number. When it finds the numbers the corresponding text in column B needs to looked up in Sheet 2 in either columns E and G for an exact match. I would then like the row coloured yellow if one of the columns has the text from 1 to 6 and the other column in the same row has the text from number 14-20. So essentially only change the row colour if there is a double match between the two groups.

Within the same workbook I will have the same issue multiple times with sheets 3 and 4 and then 5 and 6 and so on.

Thanks
Alan

[p45cal]

Best attach a representative workbook and make it clear which sheets are which.

[AlanB]

Thanks for yoursuggestion of attaching an example workbook - I should have done that from thestart.

Sheet 1 - Column Ahas the numbers 1 to 30 in random order
Sheet 1 - Column Bhas text that will need to be found in sheet 2
Each week the orderof the numbers and associated text will change.


Sheet 2 - Columns E& G will contain lists of the text in column B

Query- As per the attachedsheet - I am looking for when the text associated with number 1-6 (In thisinstance Paris, Washington, Greece, Armenia, Canberra , Pisa) Let's call those"Group A" are found in the Sheet 2 in either column E or G when thetext in associated with number 15-20 is also found in the same row in column Eor G (Spain, Azerbaijan, San Diego, Buenos Aires, Monte Carlo, Angola) or"Group B"


If group A and B arefound then the row should be highlighted yellow - E.G Rows 3,4,8,10
If Group A and A isfound then no match - Like row 2
If Group B and B isfound then no match - Like row 12

Hopefully that allmakes sense http://www.vbaexpress.com/forum/attachment.php?attachmentid=29650&stc=1http://www.vbaexpress.com/forum/attachment.php?attachmentid=29650&stc=1

Thanks once again.
Alan

[p45cal]

Conditional formatting in the attached in columns A:Z, rows 2:13.
In columns I to O (and U & V) I've got some formulae steps that I've used to derive the conditional formatting formula.
These formulae are not required on the sheet for this to work, they're only there to show my workings. They can be deleted.
The formula I ended up using is the one in cell M2. This formula might be more than twice as long as it need be because I checked both ways:
1-6 in column E with 15-20 in column G
as well as
15-20 in column E with 1-6 in column G

This may not be necessary (perhaps only the first of these needs to be checked for); in which case use the shorter formula in column O.

These formulae could be shorter still depending on what versionof Exel you're using. Do you have the likes of FILTER and LET available to you?
If so formulae in U2 or V2 could be used in CF.

Note in row 12 there are several #N/A errors, this is because there is a trailing space in cell E12
I've added a fictitious row 13 (row 10 reversed) which should show the highlighting difference between checking one way and both ways.

[AlanB]

I have just checked these on a couple of my sheets and they all work perfectly. I do have access to Filter and LET so I used that formula. I also need to check both ways so thanks for including that.

I shall mark as solved!

Thanks once again :-)