+ Reply to Thread
Results 1 to 17 of 17

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

  1. #1
    Forum Contributor
    Join Date
    08-12-2012
    Location
    mysore
    MS-Off Ver
    Excel 2007
    Posts
    212

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

    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

  2. #2
    Forum Expert mike7952's Avatar
    Join Date
    12-17-2011
    Location
    Florida
    MS-Off Ver
    Excel 2007, Excel 2016
    Posts
    3,517

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

    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.
    Thanks,
    Mike

    If you are satisfied with the solution(s) provided, please mark your thread as Solved.
    Select Thread Tools-> Mark thread as Solved.

  3. #3
    Forum Contributor
    Join Date
    08-12-2012
    Location
    mysore
    MS-Off Ver
    Excel 2007
    Posts
    212

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

    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

  4. #4
    Forum Expert mike7952's Avatar
    Join Date
    12-17-2011
    Location
    Florida
    MS-Off Ver
    Excel 2007, Excel 2016
    Posts
    3,517

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

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

  5. #5
    Forum Guru tigeravatar's Avatar
    Join Date
    03-25-2011
    Location
    Colorado, USA
    MS-Off Ver
    Excel 2003 - 2013
    Posts
    5,361

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

    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?
    Attached Files Attached Files
    Hope that helps,
    ~tigeravatar

    Forum Rules: How to use code tags, mark a thread solved, and keep yourself out of trouble

  6. #6
    Forum Contributor
    Join Date
    08-12-2012
    Location
    mysore
    MS-Off Ver
    Excel 2007
    Posts
    212

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

    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!
    Attached Files Attached Files

  7. #7
    Forum Expert mike7952's Avatar
    Join Date
    12-17-2011
    Location
    Florida
    MS-Off Ver
    Excel 2007, Excel 2016
    Posts
    3,517

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

    @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.

  8. #8
    Forum Guru tigeravatar's Avatar
    Join Date
    03-25-2011
    Location
    Colorado, USA
    MS-Off Ver
    Excel 2003 - 2013
    Posts
    5,361

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

    @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.

  9. #9
    Forum Contributor
    Join Date
    08-12-2012
    Location
    mysore
    MS-Off Ver
    Excel 2007
    Posts
    212

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

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

    I do not understand this.

    How did you create this?

    Thanks!

  10. #10
    Forum Guru tigeravatar's Avatar
    Join Date
    03-25-2011
    Location
    Colorado, USA
    MS-Off Ver
    Excel 2003 - 2013
    Posts
    5,361

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

    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.
    Last edited by tigeravatar; 08-27-2012 at 11:43 AM. Reason: Added side note

  11. #11
    Forum Contributor
    Join Date
    08-12-2012
    Location
    mysore
    MS-Off Ver
    Excel 2007
    Posts
    212

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

    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!

  12. #12
    Forum Expert mike7952's Avatar
    Join Date
    12-17-2011
    Location
    Florida
    MS-Off Ver
    Excel 2007, Excel 2016
    Posts
    3,517

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

    This will work with tigers workbook from post #5

    Please Login or Register  to view this content.

  13. #13
    Forum Expert mike7952's Avatar
    Join Date
    12-17-2011
    Location
    Florida
    MS-Off Ver
    Excel 2007, Excel 2016
    Posts
    3,517

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

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

  14. #14
    Forum Contributor
    Join Date
    08-12-2012
    Location
    mysore
    MS-Off Ver
    Excel 2007
    Posts
    212

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

    Thanks.

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

    Thanks!

  15. #15
    Forum Expert mike7952's Avatar
    Join Date
    12-17-2011
    Location
    Florida
    MS-Off Ver
    Excel 2007, Excel 2016
    Posts
    3,517

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

    Quote Originally Posted by mike7952 View Post
    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.

  16. #16
    Forum Contributor
    Join Date
    08-12-2012
    Location
    mysore
    MS-Off Ver
    Excel 2007
    Posts
    212

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

    Hi,

    I have attached the file.

    Thanks!
    Attached Files Attached Files

  17. #17
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013 O.365
    Posts
    21,019

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

    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.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1