You just need some slight correction to VBA syntax.  See the pictures below.  "acExpression" means a different type of conditional format from the one you are using.  When you manually set the formatting rule, if you see "Field Value is" and "between", you need to use the corresponding VBA counterparts, which are "acFieldValue" and "acBetween", as shown in my pic.  Also, your date values must be *strings* in the VBA code (note the double quotes enclosing the dates, including the pound sign), as shown in my pic, because that is how Access expects to receive from you.

"acExpression" pertains to the kind of rules shown in my second set of pictures below.  When you manually edit the rule, you can change the rule description to "Expression is", and the enter the expression on the right, which can be a boolean expression as shown.  The VBA counterparts, as shown, are "acExpression", "acEqual", and the expression, which also must be a string.  "acEqual" is the only option you can use here, because it says "Expression *is*", which means the expression must equal the value.  This type of conditional format gives you more freedom because you can enter any boolean expression, not just confined to "greater than", "equal", "less than", etc.

P.S.  Note that I use .Modify to modify an existing rule.  If you already have a rule, you can do this, instead of using .Delete and .Add to create a new one.  .FormatConditions(0) is the first rule on the list when you edit rules manually.  FormatConditions(1) is the second, and so forth.

Thanks, Kevin,

That makes some sense to me. As, I said in the subject, acBetween what? I do need to compare the StartDate and EndDate to the BoxStart and BoxEnd values, so I cannot use the value in the text box with the acBetween. I will have to work on something like this.
        Set FC = Me.Controls(BoxName).FormatConditions.Add(acExpression, acEqual, "#" & [StartDate] & "# <= #" & Format(BoxStart, "MM/DD/YYYY") & "# And #" & Format(BoxEnd, "MM/DD/YYYY") & "# <= #" & [EndDate] & "#")

If I am using variables in the text string I am happier evaluating them outside the string. This is still not right, but it is moving me in the right direction. Thanks again,
Ricky

Okay, Kevin,

The issue was that I went too far taking the field names out was wrong. It should be:
        Set FC = Me.Controls(BoxName).FormatConditions.Add(acExpression, acEqual, "[StartDate] <= #" & Format(BoxStart, "MM/DD/YYYY") & "# And #" & Format(BoxEnd, "MM/DD/YYYY") & "# <= [EndDate]")

Now the delete vs modify thing is about if the conditional formatting is stored in the access file or not. Do I delete and add them every time, or do I modify them? I will have to build a subroutine that I run every time that the user changes the date ranges of the Gantt. looking at the conditional formatting from the design view, I would say that they are not stored in the Access file, so I plan to delete and add them.

I can now add different colors for each department when I build the sub and call it on load and every time the user changes the date range. Thanks again for all your help,

Ricky