Use Named Range rather than hard-coded values

[detriez]

I have two Dynamic Named Ranges I need to use in sub

• E2 needs to be replaced with Named Range nDataCountry
• 'Standard Values'!$B$2:$B$251 needs to be replaced with Named Range nCountry

I am looking for values in nDataCounrty that do not exist in nCountry

This working code does not accommodate the expansion of the dynamic named ranges

    Range("[COLOR=#0000ff]E2:E24[/COLOR]").Select
    Selection.FormatConditions.Add Type:=xlExpression, Formula1:= _
        "=AND([COLOR=#0000ff]E2[/COLOR]<>"""",ISERROR(MATCH([COLOR=#0000ff]E2[/COLOR],[COLOR=#ff8c00]'Standard Values'!$B$2:$B$251[/COLOR],0)))"

I need to do something like this

    Range("[COLOR=#0000ff]nDataCountry[/COLOR]").Select
    Selection.FormatConditions.Add Type:=xlExpression, Formula1:= _
        "=AND([COLOR=#0000ff]nDataCountry[/COLOR]<>"""",ISERROR(MATCH([COLOR=#0000ff]nDataCountry[/COLOR],[COLOR=#ff8c00]nCountry[/COLOR],0)))"

 

[SpillerBD]

Using named Range.

ActiveWorkbook.Names.Add Name:="nCountry", RefersToR1C1:= _"=Elavon!R2C2:R" & R_ounter & "C2"