Need help with formulas for conditional formatting

[OaklandJim]

Team Mr. excel

I am trying to make a Gantt chart worksheet. Need to use conditional formatting. I thought that I was adept at writing formulas for conditional formatting. But I'm having trouble with this. I am using this formula -- which works in worksheet cells -- =ISNUMBER(MATCH(TEXT(rcDayDate,"ddd"),WorkDaysInAWeek,0)) to detect "current" date is a workday.

rcDayDate is a name whose RefersTo is a basic relative address like L$5. For a given cell within the Gantt chart range it refers to the cell containing the date being "processed." WorkDaysInAWeek is merely a named range containing a list of day names that are within the workweek like "Mon","Tue","Wed","Thu" for a four day workweek.

Aside: I searched on-line and it seems that conditional formatting is ok with relative addressing.

As I said above, the formula works (returns true or false) when used in cells. But the conditional formatting does not seem to get that formula to evaluate to true or false.

Any guidance would be appreciated. Is my formula not acceptable? Should I try helper cells in each column that say true or false and refer to those in the conditional formatting.

I really appreciate your assistance.

Jim

 

[6StringJazzer]

Normally I would want access to the file to troubleshoot this, but lacking that, I would request some specific details.

Exactly how is rcDayDate actually defined? Please copy the formula to paste it into your post, or screenshot it. Since it has a relative address indicate what cell is active when you are copying the formula.

What is the Applies To range for your CF rule?

What cell are you putting this formula in in your worksheet for testing?

Your approach is perfectly valid for CF but I suspect that there is misalignment in the definition of your named range and how the CF rule is set up.