Gantt Chart with Conditional Formating

Hi Ellerd

Try for the conditional formatting formula in D5:

=AND(D$1>=$A5, D$1<=Workday($A5,$C5)-1)

P. S.

It could also be just

=AND(D$1>=$A5, D$1<=$B5)

but I used C5 as you did, I assumed you use it for a reason.

PGC01,<o></o>
Thanks for you’re quick reply. <o></o>
One issue: When I add you’re formula: =AND(D$1>=$A5, D$1<=WORKDAY($A5,$C5)-1) I get the message: You may not use references to other worksheets or workbook for conditional formatting criteria?<o></o>
<o> </o>
I don’t have any link or references to other sheets or workbooks. Do you have any idea?<o></o>
Kind regards,<o></o>
Ellerd Beijert<o></o>

I don't know exactly but maybe It will help you:
Gantt diagram Excel file:
http://www.planetaexcel.ru/docs/Gantt 3.0.zip
http://www.planetaexcel.ru/docs/Gantt 2.0.zip

(Russian site about Excel)

I get the message: You may not use references to other worksheets or workbook for conditional formatting criteria?

Click to expand...

I tested in xl2007. In previous versions you could not use Analysis Toolpak functions in conditional formatting.

One workaround is to define a name that calculates the Workday part. Since the formula uses relative addresses it's preferable to use the R1C1 notation.

This worked in xl2000.

Define a name,

Name: Workday1
Refers to: =WORKDAY(INDIRECT("RC1",FALSE),INDIRECT("RC3",FALSE))

Now in D5 use for the conditional formatting formula:

=AND(D$1>=$A5, D$1<=Workday1-1)


Another solution is to replace the function Workday by an equivalent formulation using just native functions.

pgc01,
Thanks, this one is ok.
regards
Ellerd