Conditional Formatting based on Defined Names.

Hi all,
I would really appreciate if someone could help me with the following problem:
I wrote this code, but for some reason it doesn't work. I am completely new to VBA and have no idea what went wrong:

Sub Macro1()
'
' Macro1 Macro
' Macro recorded 6/9/2009
'

'
Dim FileName As String

FileName = Application.GetOpenFilename
    

    ActiveWorkbook.Names.Add Name:="ForeignLoanNumber", RefersToR1C1:="=[FileName]Sheet2!C3"
    ActiveWorkbook.Names.Add Name:="LocalRange", RefersToR1C1:= "='Detailes by Provider'!C3:C8"
    ActiveWorkbook.Names.Add Name:="ForeignRange", RefersToR1C1:="=[FileName]Sheet2!C3:C8"

 Columns("C:C").Select
    Selection.FormatConditions.Delete
    Selection.FormatConditions.Add Type:=xlExpression, Formula1:= _
        "=(COUNTIF(ForeignLoanNumber,C1)=0)*(C1<>"" "")"
    Selection.FormatConditions(1).Font.ColorIndex = 3

Columns("H:H").Select
    Selection.FormatConditions.Delete
    Selection.FormatConditions.Add Type:=xlExpression, Formula1:= _
        "=VLOOKUP($C1,LocalRange,6,False)<>VLOOKUP($C1,ForeignRange,6,False)"
    Selection.FormatConditions(1).Font.ColorIndex = 41
 
    
End Sub

When I run this macro I can see that it does something. For example, when I click on columns C and H and go to conditional formatting, I can see that the formulas are there. Aslo, all the names are defined appropriately. The only problem is that names that are supposed to refer to the ranges in a different work book, refer to nowhere. Basically their "Refer to" window contains path like that:

[FileName]Sheet2!$C:$H

It is my understanding that this string should contain a real path to the second workbook, not a FileName variable, but it doesn't. What am I doing wrong?

Thank you in advance,
Eugenia.