Colour rows if contain a certain cell value

[barrboy89]

Apologies if this is in the wrong forum, this is my first post.

Basically I have a huge list of data in sheet1 that needs to be investigated. On sheet2 I have a list of refs that don't need to be investigated because they have already been sorted.

Is there any way of colouring the row in sheet1 that contains a ref from the list in sheet2 so that I know they don't need to be looked at. I had a look at using and if function and a vlookup but got a bit stuck.

Any help would be greatly appreciated.

Thanks

[Marcol]

Hi barrboy89

Welcome to the forum

You would do best to post a sample workbook showing Before and After.
It should clearly illustrate your problem and not contain any sensitive data.

Cheers

[NBVC]

You just need a MATCH() function in you Conditional format

e.g

=MATCH(A1,ColA,0)

where ColA is a named range in Sheet2 (in column A)... you need to name the ranges because Conditional formatting doesn't allow direct references to other sheets.

[barrboy89]

Hi,
Thanks for you reply.

In my conditional formatting formula I now have
=MATCH(G:G,SMO,0)

where SMO is my named range, and G:G is the column on sheet1 that contain the refs matching SMO.

But it doesn,t work.

The formula itself is looking good because when i use the formula =MATCH(SMO,Sheet1!G:G,0)
in the actual worksheet, it is giving the row numbers that match, so its good progress!

How exactly do I use the conditional formatting thing in this situation?

Thanks

[NBVC]

So SMO is an named range referencing Sheet1!G:G?

If so, after select column G in Sheet2, enter:

=MATCH(G1,SMO,0)

[barrboy89]

Thank you so much mate - got it working.
One last thing, there are a few that contain more than one ref, or a bit of extra txt, is there any way of seeing if it contains the ref rather than exactly matches? if it's too hard it's not a big deal - there aren't that many and i could check by hand.

Also any way of doing the whole row in colour rather than just the matching cell?

Thanks again

[NBVC]

Perhaps:

=MATCH("*"&G1&"*",SMO,0)