Posts by Oponion

    How does you code integrate into your project, spread sheet? Could a For...Next loop be used?


    Code
    For i = 1 to 100
      [INDENT]For j = 1 to 20[/INDENT]
      [INDENT=2]If ActiveSheet.Cells(i,j).Interior.Color = Excel.XlRgbColor.rgbYellow Then ActiveSheet.Cells(i,j).Interior.Color = xlNone[/INDENT]
      [INDENT]Next j[/INDENT]
      Next i



    Though I must admit I haven't seen too much of this used:

    Code
    Excel.XlRgbColor.rgbYellow


    If the color is not exactly Excel.XlRgbColor.rgbYellow it might just return a false in the IF statement.



    I would generally use a more standard approach:


    Code
    Cells(i,j).Interior.Color =vbYellow
    
    
    ' or 
    
    
    Cells(i,j).Interior.Color = RGB(255, 255, 0)


    [INDENT=2] [/INDENT]

    Hey,


    I think you might run into some problems here because some country codes have the same beginnings but different lengths, like The Dominican Republic's country codes are +1809, +1829, and +1849 - this could lead to a confusion with the US. Even worse there are countries that share one country code, e.g. USA & Canada both use +1, while Russia & Kazakhstan use +7


    If you have a finite amount of countries that could work. you could use vlookup in a (slightly) tedious nested formula.


    Code
    =IFERROR(VLOOKUP(NUMBERVALUE(LEFT(A1,4)),CountryCodes!A:B,2,0),IFERROR(VLOOKUP(NUMBERVALUE(LEFT(A1,3)),CountryCodes!A:B,2,0),IFERROR(VLOOKUP(NUMBERVALUE(LEFT(A1,2)),CountryCodes!A:B,2,0),IFERROR(VLOOKUP(NUMBERVALUE(LEFT(A1,1)),CountryCodes!A:B,2,0),"No Match"))))


    This formula assumes the telephone number to be in cell A1, the Formula could be in B1. The CountryCodes Sheet has the numbers in colum A and country names in Column B.


    It basically tries to match the leftmost 4 characters, then 3, then 2, then 1, but it will ignore 5 digit country codes, because I'm not sure how much overlap CountryCode & Local Code can have.


    Though I kinda would think of making a UDF if you understand VBA, could make the formula easier.


    Edit: I want mention that I found that formula on another board after a google search. just modified it slightly to explain it easier.