ISO 3166 compliant countries

[slug]

Hi, I receive dozens of address lists from customers. The customers
use a templated excel workbook that has a set array of address fields.
One of these fields is "Country".

Sometimes there are errors in the country names and I need them to be
ISO compliant. Ideally, I'd like to set up a workbook that contains a
list of country codes and a macro workbook that

1. works on the already open address list workbook
2. Checks that the address list workbook has the value in Range("A10")
= "Country"
3. Check all entires in column 10 are within the ISO list, If not, it
highlights those that are not compliant.

As an added extra, it would check for certain commom "errors" and
correct them (e.g. change UK to UNITED KINGDOM).

I've been tying myself in knots trying to use the
worksheetfuntion.countif() function to count if each cell in column 10
is contained in the ISO list. I can't get it to work. Any help is
greatly appreciated!

 

[Tom Ogilvy]

countif only works if the referenced workbook is open

Assumes the ISO list is in the first sheet in the tab order in column A of a
workbook named ISOList.xls found in C:\Myfiles directory. Adjust to suit.

sub MarkTheActivesheet()
Dim sh as Worksheet, bk as Workbook
Dim rng as Range, rng1 as Range
dim cell as Range
Set sh = Activesheet
On Error Resume Next
set bk = Workbooks("ISOList.xls")
On error goto 0
if bk is nothing then
set bk = Workbooks.Open("C:\MyFiles\ISOList.xls")
end if
set rng1 = bk.worksheets(1).columns(1)
set rng = sh.Range(sh.Cells(2,10),sh.cells(rows.count,10).End(xlup))
rng.Interior.colorIndex = xlNone
for each cell in rng
if application.countif(rng1,cell) = 0 then
cell.Interior.ColorIndex = 3
end if
Next
End sub

Code is untested and may contain typos which you should correct of course.

 

[slug]

Wow. This works. Thank you so much for your help.