I need Macro(VBA code) to Highlight Invalid Country Codes in a column

**narendrabr** · 08-27-2012, 10:25 AM

Hi,
I have lot of country codes and I need to highlight invalid country codes.Please give me proper VBA code to highlight invalid country codes in excel 2007.

Thanks

**mike7952** · 08-27-2012, 10:49 AM

How are we supposed to check for Invalid country codes? What are country codes? A little more info in what your trying to due, maybe upload a sample workbook.

**narendrabr** · 08-27-2012, 10:58 AM

I have country codes like USA,UK,AUS,CHN and codes for other countries also.

If I select the country column and run the macro.It should highlight wrong entries.For example suppose if there are any other entries instead of these standard codes that data should be highlighted.

Thanks!
Narendra

**mike7952** · 08-27-2012, 11:01 AM

Still more details needed here? It would be better if you can upload your workbook so we can see it.

**tigeravatar** · 08-27-2012, 11:12 AM

narendrabr,

Welcome to the forum!
I don't think you need a macro for this because conditional formatting can perform this easily.

Attached is an example workbook based on the criteria you described.
In the sheet 'Country Information' is a list of countries and their information including ISO country country codes with their 2 and 3 letter codes. (I just pulled the information from here.)

Then in 'Sheet1' column A is a list of some country codes including a couple of invalid ones, "AAA" and "BBB". To get the conditional formatting to work, I created a named range with the name list_CountryCodes. It is defined as:

Please Login or Register  to view this content.

Then, I applied this conditional format formula to 'Sheet1' column A:

Please Login or Register  to view this content.

As you can see, the invalid country codes are now highlighted. Does that work for you?

**narendrabr** · 08-27-2012, 11:21 AM

Hi,

I have set of country codes as you can see in the attached file.
I need to write macro to highlight data which does not match with the standard codes.

Thanks!

**mike7952** · 08-27-2012, 11:22 AM

@tiger

I dont believe its working 100% accurate. For example if I type in Just A or just B ect... its does not work? I dont know what the OP data looks like so maybe this will not be an issue.

**tigeravatar** · 08-27-2012, 11:33 AM

@mike7952,

Ah, good call, thank you. That can be corrected by including an OR statement to check for Code Length (in the example, I have country codes with 2 digits which would be considered valid, so it checks if the length is < 2) like so:

Please Login or Register  to view this content.

Haven't had a chance to look at the OP's provided workbook, but I'll take a look at it shortly.

---------- Post added at 09:33 AM ---------- Previous post was at 09:30 AM ----------

narendrabr,

Your example workbook contains a single worksheet with a single table. There is nowhere in the workbook that contains information to compare against to check for invalid country codes. It is not hard to find that information and create a table to have a comparison, which is what I suggest and showed in my example workbook above.

**narendrabr** · 08-27-2012, 11:33 AM

='Country Information'!$B$2:$B$238

I do not understand this.

How did you create this?

Thanks!

**tigeravatar** · 08-27-2012, 11:42 AM

In Excel 2007, go to the Formulas tab and click "Name Manager". In there you can create New named ranges, or Edit/Delete existing named ranges.

[EDIT]
Also, as a side note: In your provided workbook there are no invalid country codes.

**narendrabr** · 08-28-2012, 06:00 AM

Hi,

We have some standard three letter country codes and we will receive some list that contains country codes with some invalid entries.

I am looking to write a macro using these standard country codes in VBA to check and highlight as we did using conditional formatting.

Thanks!

**mike7952** · 08-28-2012, 06:24 AM

This will work with tigers workbook from post #5

Please Login or Register  to view this content.

**mike7952** · 08-28-2012, 06:30 AM

If you can not adjust post back with you workbooks actual layout

**narendrabr** · 08-30-2012, 07:21 AM

Thanks.

Can you please help me on how I can test this code on my sheet?

Thanks!

**mike7952** · 08-30-2012, 08:01 AM

Originally Posted by mike7952

If you can not adjust post back with you workbooks actual layout

Like I had said I will need your Workbooks actual layout. Your Workbook in Post #6 doesn't have anything to compare to.

**narendrabr** · 08-31-2012, 05:19 AM

Hi,

I have attached the file.

Thanks!

**jindon** · 08-31-2012, 07:01 AM

Conditional format

1) Select A1:A12 in "I need to Highlight-Wrong data" sheet.
2) Go to Conditional Format, formula =
=ISERROR(MATCH(A2,'Standard -Codes'!$A$2:$A$12,0))
3) Set the cell format

Otherwise
try

Please Login or Register  to view this content.

I need Macro(VBA code) to Highlight Invalid Country Codes in a column

LinkBack

Thread Tools

Rate This Thread

Display

I need Macro(VBA code) to Highlight Invalid Country Codes in a column

Re: I need Macro(VBA code) to Highlight Invalid Country Codes in a column

Re: I need Macro(VBA code) to Highlight Invalid Country Codes in a column

Re: I need Macro(VBA code) to Highlight Invalid Country Codes in a column

Re: I need Macro(VBA code) to Highlight Invalid Country Codes in a column

Re: I need Macro(VBA code) to Highlight Invalid Country Codes in a column

Re: I need Macro(VBA code) to Highlight Invalid Country Codes in a column

Re: I need Macro(VBA code) to Highlight Invalid Country Codes in a column

Re: I need Macro(VBA code) to Highlight Invalid Country Codes in a column

Re: I need Macro(VBA code) to Highlight Invalid Country Codes in a column

Re: I need Macro(VBA code) to Highlight Invalid Country Codes in a column

Re: I need Macro(VBA code) to Highlight Invalid Country Codes in a column

Re: I need Macro(VBA code) to Highlight Invalid Country Codes in a column

Re: I need Macro(VBA code) to Highlight Invalid Country Codes in a column

Re: I need Macro(VBA code) to Highlight Invalid Country Codes in a column

Re: I need Macro(VBA code) to Highlight Invalid Country Codes in a column

Re: I need Macro(VBA code) to Highlight Invalid Country Codes in a column

Thread Information

Users Browsing this Thread

Bookmarks

Bookmarks

Posting Permissions