Color Cells which do not match criteria-II

Hi,

if you just want to highlight the wrong country codes, you can use conditional formating. With your given Excel-file, this it how it goes in XL2007:

1. Select the area, where the users entered country codes are (I2:I12)
2. Go to Home -> Conditional Formatting -> New Rule... and select Use a formula to determine which cells to format
3. In the text box Format values where this formula is true type:
=(VLOOKUP(H3,$B$3:$C$12,2)<>I3)

here H3 is the first cell in the area that you selected in step 1
$B$3:$C$12 is the area, where the correct country names and codes are
I3 is the first user entered country code

4. Click format and select your favourite error formatting for the wrong contry codes entered.

5. Click OK and that should do the trick.

- Asser

Mr.Roy i thought my problem is different from my earlier post. i m sorry if i had broken any rules.

Coming to my query, Thanks a lot mr.Jazzer for ur solution. that will work fine if had the same data entered by the operators as in the predefined table.

what if i had sub regions entered in one of my cells in I Column.i mean if my Country Code start with 975 and a number suffixed to my country code like 12345 or 97512345 .i want the formula for this situation.

No Solution...??????

hmmmmm Dear Mr.JBeaucaire Ur solution would suffice my problem.

But to elaborate and Make it more clear what i wanted to do is to mark the cells which start with Code 975 and the suffix to this code will be something like12354 for bhutan.i gave the table on the left just for reference.

Digging further more deeper, if bhutan is selected as country and the code or telephone number for place 'X" in bhutan would start with 975-1234(This is the number for any region/Area/Place "X" in bhutan")

if bhutan is selected the code should start with 975. and like wise for the rest.hope i had made it more clear

Still not sure.....???OK.........
hmmmmmmm if the country is Bhutan then the code should start with 97 and if the country is india it should start with 91...
Further, if the cell contains bhutan in the COUNTRY COLUMN and the code should be something like975xxxxxx(where x denotes...any Telephone Number).
Like wise,if the COUNTRY is India the code should be 91 and the number xxx suffixed to the code like 91xxxxx

Thanks very much mr.Krishna for that reply & Solution.it worked fine.