Trying to substitute Table Headers into Conditional Formatting formula

[sspatriots]

At at a loss as to why the following result in the Conditional Format Formula will work:

=IF($C3 <> "5 CJS",DATEVALUE("06/19/22") >=$BT3,)

The VBA code that is creating this formula is:

.FormatConditions.Add Type:=xlExpression, Formula1:="=IF($C3 <> ""5 CJS"",datevalue(" & Chr(34) & CurrDate - 30 & Chr(34) & ") >=$BT3,)"

Then...

Yet, when I substitute in the table header references nothing happens, see below:

=IF(INDIRECT("G2JobList[[Job

Status]]" <> "5 CJS"),DATEVALUE("06/19/22") >=INDIRECT("G2JobList[[Job Status

Last Update]]"),)


The VBA code that is creating this formula is:

FormatConditions.Add Type:=xlExpression, Formula1:="=IF(INDIRECT(""G2JobList[[Job" & Chr(10) & "Status]]"" <> ""5 CJS""),datevalue(" & Chr(34) & CurrDate - 30 & Chr(34) & ") >=INDIRECT(""G2JobList[[Job Status" & Chr(10) & "Last Update]]""),)"

I just want to make the VBA code work with the substitutions. Any help would be much appreciated. Thanks, SS

 

[Fluff]

You cannot use structured references in conditional formatting.

 

[sspatriots]

I'm sorry, I'm not following because I don't know what the difference is. I also have another format that does work that is set up as follows:

=IF(NOT(ISBLANK(INDIRECT("G2JobList[@[Cab

REQD Date]]"))), DATEVALUE("07/19/22")>INDIRECT("G2JobList[@[Cab

REQD Date]]"),)


The VBA code that is creating this formula is:

.FormatConditions.Add Type:=xlExpression, Formula1:="=IF(NOT(ISBLANK(INDIRECT(""G2JobList[@[Cab" & Chr(10) & _
    "REQD Date]]""))), datevalue(" & Chr(34) & CurrDate & Chr(34) & ")>INDIRECT(""G2JobList[@[Cab" & Chr(10) & "REQD Date]]""),)"

 

[Fluff]

See here for details about structured references. Structured references in Excel tables - Ablebits.com
And you cannot use them in CF you need to use normal ranges.

 

[sspatriots]

I'm not sure I understand why you can't use structured references in CF code, but I did manage to make that bit work that I first posted. I had to add "@" symbol in two places and move the right parenthesis from the end of where it said ""... Status]]"" <> ""5 CJS""), ..." to just after those first set of double quotes (... ""Status]]"") <> ""5 CJS"", ....") and it is working perfectly.

.FormatConditions.Add Type:=xlExpression, Formula1:="=IF(INDIRECT(""G2JobList[@[Job" & Chr(10) & "Status]]"") <> ""5 CJS"",datevalue(" & Chr(34) & CurrDate - 30 & Chr(34) & ") >=INDIRECT(""G2JobList[@[Job Status" & Chr(10) & "Last Update]]""),)"

 

[Fluff]

I'm not sure I understand why you can't use structured references in CF code

Because Excel won't let you.

 

[sspatriots]

Then is what I ended up with a structured reference? I have several of these in my code and they all work now the way they were intended to work. Not sure I follow why you say this doesn't work because Excel doesn't allow it.

 

[Fluff]

As I have no Idea what that formula is doing as I don't have your data, but you try using table references when you create CF manually.

 

[sspatriots]

I got it. Just can't do it manually. So the code is the work around.

 

[Fluff]

I don't understand how it works at all, unless it's to do with the indirect.