FormatConditions.Add for xlExpression & formula - fails!

shell_l_d

Board Regular
Joined
Jun 25, 2010
Messages
73
I can't seem to get this conditional formatting code to work.

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

xlCellValue works:
Code:
        With .Range("tblGlobalData[Response Time]").FormatConditions _
            .Add(xlCellValue, xlEqual, "FAIL")
            .Font.ColorIndex = 3  ' red
            .StopIfTrue = True
        End With
xlExpression fails:
Code:
    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
Code:
=AND( ISNUMBER( tblGlobalData[[#This Row],[CloseDate]]), ISBLANK(tblGlobalData[[#This Row],[TechComp]] ) )
 
Last edited:

RoryA

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
34,370
Office Version
365, 2019, 2016, 2010
Platform
Windows, MacOS
Have you tried using that table formula in CF manually?
 

shell_l_d

Board Regular
Joined
Jun 25, 2010
Messages
73
Not sure what you mean sorry. But I have tried the AND formula in a cell & it works.
 

shell_l_d

Board Regular
Joined
Jun 25, 2010
Messages
73
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:
Code:
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:
Code:
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

Board Regular
Joined
Jun 25, 2010
Messages
73
This works (unless I've made a typo) :)

Code:
    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
 
Last edited:

Forum statistics

Threads
1,082,478
Messages
5,365,784
Members
400,850
Latest member
Raj_Jpr

Some videos you may like

This Week's Hot Topics

Top