Conditional Formatting for Schedule Dates

[MntnrMark]

I'm using a formula that has worked previously to conditionally format dates in a Gantt chart schedule, but its not working now, as it is erroneously shading rows where there are no dates at all.

The formula I'm using is: =AND(E$16>=(EOMONTH($C19,-1)+1),E$16<(EOMONTH($D19,0)))

In the screenshot below note that Row 25 has no dates, and that row needs to remain as a blank row in the worksheet, but the formula above has shaded it anyway. (How, I don't know, as the row doesn't have any dates in it!)

Also, Cell E23 should be shaded, because there is some activity occurring in May of 2022 (i.e., through the 24th of May), but its not shading.

BOTTOM LINE: I need cells to shade (I'm using light blue) if a start date (Column C below) has activity in a given month and, likewise, I need cells to shade if an end date (Column D below) has activity in a given month, and the month needs to shade if that month falls anywhere inbetween a start date and a finish date.

Can anyone figure this out? If so, I'd really appreciate it.

 

[Fluff]

How about

=AND($C19<>"",$D19<>"",E$16>EOMONTH($C19,-1),E$16<=EOMONTH($D19,0))

 

[MntnrMark]

How about

=AND($C19<>"",$D19<>"",E$16>EOMONTH($C19,-1),E$16<=EOMONTH($D19,0))

Thanks, Fluff, but that didn't fix it, either. Below is a larger screenshot where I tried your formula using a different shade color (light tan).

As you can see, Row 46 is shading even though there's no start date, finish date, or anything else on that row.

Also, look at Row 44 - even though there is activity through October the 5th, the shading stops at the end of August.

What appears to be happening is that everything is shifting downward one row. Look at Rows 27 and 28 - if you move the shading in Row 28 up to Row 27 your formula would seem to work perfectly.

Any idea on why that is happening and how to fix it?

 

[Fluff]

That looks as though the formula & applies to range don't match.
The formula should be looking at the first row in the applies to range.

 

[MntnrMark]

That looks as though the formula & applies to range don't match.
The formula should be looking at the first row in the applies to range.

I finally got it to work using this formula:

=AND($C20<>"",$D20<>"",E$16>EOMONTH($C20,-1),E$16<=EOMONTH($D20,0))

Thanks, Fluff. Your help is very much appreciated.

 

[Fluff]

Glad you sorted it & thanks for the feedback.