Jul 13 2021 01:39 PM
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.
Jul 13 2021 02:36 PM
SolutionThe 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))
Jul 14 2021 03:12 PM
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?
Jul 14 2021 03:58 PM
Jul 15 2021 11:45 AM
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
Jul 15 2021 11:57 AM
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)
Jul 15 2021 12:42 PM
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.
Jul 15 2021 01:17 PM
Jul 15 2021 02:19 PM
Jul 13 2021 02:36 PM
SolutionThe 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))