FormatConditions.Add for xlExpression & formula - fails!

[shell_l_d]

I can't seem to get this conditional formatting code to work.

Error:Run-time error '5'
Invalid procedure call or argument

xlCellValue works:

        With .Range("tblGlobalData[Response Time]").FormatConditions _
            .Add(xlCellValue, xlEqual, "FAIL")
            .Font.ColorIndex = 3  ' red
            .StopIfTrue = True
        End With

xlExpression fails:

    Range("tblGlobalData[TechComp]").Select
 
    ' FAILS HERE
    Selection.FormatConditions.Add Type:=xlExpression, Formula1:="=AND( ISNUMBER(tblGlobalData[[#This Row],[CloseDate]]), ISBLANK(tblGlobalData[[#This Row],[TechComp]]) )"
 
    Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
    Selection.FormatConditions(1).Interior.Color = 255
    Selection.FormatConditions(1).StopIfTrue = True

I want to fill any [TechComp] column cells with Red if it is blank (has no date) & [CloseDate] is not blank (has a date).

Any help appreciated, thanks

Using this as a formula in a cell returns TRUE or FALSE correctly

=AND( ISNUMBER( tblGlobalData[[#This Row],[CloseDate]]), ISBLANK(tblGlobalData[[#This Row],[TechComp]] ) )

 

[RoryA]

Have you tried using that table formula in CF manually?

 

[shell_l_d]

Not sure what you mean sorry. But I have tried the AND formula in a cell & it works.

 

[shell_l_d]

Played around with CF (Conditionally Formatting) manually some more & used record macro & then played around with the resulting VB code.

Ok problem is it wont accept the tablenamings in the Formula.

This works but has hard coded cellnames:

Range("tblGlobalData[TechComp]").Select
Selection.FormatConditions.Add Type:=xlExpression, Formula1:= _
          "=AND( ISBLANK($K2), ISNUMBER($L2) )"

But this fails with the run time error mentioned above:

Range("tblGlobalData[TechComp]").Select
Selection.FormatConditions.Add Type:=xlExpression, Formula1:= _
          "=AND( ISBLANK(tblGlobalData[[#This Row],[TechComp]]), ISNUMBER(tblGlobalData[[#This Row],[CloseDate]]) )"

So I can work around this so it's still dynamic, I'll just have to get the start & end cells of the 2 columns in the table.

P.S. the _ in "Formula:= _" comes from using Record Macro

 

[shell_l_d]

This works (unless I've made a typo)

    Dim techComp As String, closeDate As String

    Range("tblGlobalData[CloseDate]").Select
    closeDate = ActiveCell.Address(False, True)   

    Range("tblGlobalData[TechComp]").Select
    techComp = ActiveCell.Address(False, True)   

    Selection.FormatConditions.Add Type:=xlExpression, Formula1:= _
           "=AND( ISBLANK(" & techComp & "), ISNUMBER(" & closeDate & ") )"
    Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
    With Selection.FormatConditions(1)
        .Interior.Color = 255
        .StopIfTrue = True
    End With