Excel-VBA conditional formatting with vlookup: why does my argument change ($B1 to $B1048573)

Question

I'm kinda confuded, 'coz i can't find a way to prevent excel from changing... But let's start at the beginning.

I've following code for my macro:

ActiveWorkbook.Names.Add Name:="DBM", RefersToR1C1:= _
    "='Sheet A'!C35:C39"

With Worksheets("Sheet B").Range("$B:$B").FormatConditions _
    .Add(Type:=xlExpression, Operator:=xlExpression, Formula1:="IF(ISBLANK(VLOOKUP($B1;DBM;5;FALSE));TRUE;FALSE)")
    With .Interior
        .ColorIndex = 3
    End With
End With

This works kinda, but not for my fully needs. It automatically changes the $B1 argument within my vlookup functions changes to $B1048573 - That's why the wrong lines are formatted (background color red). If i manually change it back to $B1 it will be absolutely correct.

So I tried to add this in my macro:

Worksheets("Sheet B").Range("$B:$B").FormatConditions(1) _
    .Modify Type:=xlExpression, Operator:=xlExpression, Formula:="IF(ISBLANK(VLOOKUP($B1;DBM;5;FALSE));TRUE;FALSE)")

Unfortunetaly it does the change ($B1048573) once again.

Does any1 have an idea how to fix it? Or do I really have to change it back manually everytime i use the macro?

Answer 1

Comment by Rory

Select B1 first

This was pretty much the solution. Just wanted to add this here, so everyone with the same issue can immediatly find the answer.

Thanks Rory.