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:

Some videos you may like

Excel Facts

Back into an answer in Excel
Use Data, What-If Analysis, Goal Seek to find the correct input cell value to reach a desired result

RoryA

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
35,805
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2010
Platform
  1. Windows
  2. 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:

Watch MrExcel Video

Forum statistics

Threads
1,118,048
Messages
5,569,871
Members
412,298
Latest member
dietitiann
Top