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

 

[sspatriots]

I'm only using it because too many people have their hands in this file I have and are always jacking up the formatting. Got tired of cleaning it up manually.