Conditional Formatting

[HRGuru55]

Can anyone help...  The screenshot shows the conditional formatting rules that I have created. The first (yellow) works with no problem.  The third (flesh) works with no problem.  The other two (red & blue) only work some of the time.   I believe all cells are formatted the same.  Workbook is enclosed.

ie... red does not work in Row 3 (columns W - AO),4 (in columns AL-AO),5(only in column AP), 6 (in columns AL & AM), 7 (in columns Q & AF), 9 (in columns AF, AG, AK,AL,AM), 10 (in columns AM-AP), 11 (In columns AM-AP) and 12 (in columns AL - AP). 

Blue does not work in Row 9 and wont work in Rows 10-12 if I change date back to early July. 

[#2 · Accepted solution]

@HRGuru55 

The rule for blue has $N3>P$2. This should be $N3>=P$2.

You can simplify it to =AND($N3>=P$2,$N3<Q$2,P$2<TODAY()+5)

I think you need the following formula for the rule for red:

=AND($F3>0,P$2<=TODAY(),P$2>=$F3,TODAY()>$N2,ISBLANK(P3))

[#3]

@Hans Vogelaar 

Hans,
Thank you, i made a little change so the red would not show up later than the blue and everything seemed to be working perfectly. Added a lot more data to test.... AND.. the formatting started getting wonky.  See attached.  At Row 41 the Red formatting stopped working..... unless I change the date in column N to a date that is prior to today.  then it works in the next row.  ( i have illustrated in screenshots 1 and 2 what happens).  any ideas of what is going on? 

[#4]

@HRGuru55 

The formula for red should be

=AND($F3>0,P$2<=TODAY(),P$2>=$F3,TODAY()<$N3,ISBLANK(P3))

[#5]

@Hans Vogelaar 

but with that formula, the red continues after a blue cell.   the blue cell indicates the end of the service.  see screenshots of conditional formatting when changing back to formula you gave me.  what I added yesterday was working, but stopped after 40 rows.

The top 12 rows should have no color because their stop date was prior to 1/1/2021, all other rows should have no red to the right of a blue cell.  that is why i added P$2<$N3

[#6]

@Hans Vogelaar 

think maybe i was rambling maybe this will help. In picture 5 I added two more rows, same date of start, same date of end.  but you can see the formatting in the cells is not the same.  one row does not have flesh (start date) and the other does not have red (missing data)

[#7]

@HRGuru55 

It might help if you explained in detail what each of the conditional formatting rules is supposed to do. I've just been trying to edit them blindly without any idea of what their purpose is.

[#8]

Thanks for being patient.... The yellow rules just defines the current week. The flesh colored defines the week the service starts and the blue defines the week the service ends. In each week of service, the employee is to hyperlink to a PDF report. The red rule notes which weeks do not have an "X" in them which indicates that a PDF has been linked

[#9]

@HRGuru55 

Thank you. Try this for red:

=AND($F3>0,P$2<=TODAY(),P$2<$N3,P$2>$F3,P3="")

[#10]

thank you.... that worked!!