Multiple conditional formatting - or similar?

[kristell]

I have to respond to some letters within a certain amount of time. I want to use various conditional formatting to highlight one of my columns reply due date.

So I have my date received and then reply due date column

I want the reply due column to change colour depending:

Green due in 3-5 days
Yellow due in 2-3 days
Red due in less than 2 days

I then have another column - date reply sent

If the reply is within the 5 days the reply due date column turns blue
If it the reply is over 5 days the reply due date column turns pink
if the reply is NA (didn't need a reply) then the reply due date column turns orange


I can't get the conditional formatting to work - would be grateful for some help here.

Many thanks.

 

[Fluff]

How about

+Fluff New.xlsm
	B	C	D
1	15/09/2020	20/09/2020	na
2	16/09/2020	21/09/2020	18/09/2020
3	17/09/2020	22/09/2020	21/09/2020
4	18/09/2020	23/09/2020	26/09/2020
5	19/09/2020	24/09/2020	26/09/2020
6	20/09/2020	25/09/2020	24/09/2020
7	21/09/2020	26/09/2020	na
8	22/09/2020	27/09/2020
9	23/09/2020	28/09/2020
10	24/09/2020	29/09/2020
11	25/09/2020	30/09/2020
12	26/09/2020	01/10/2020
13	27/09/2020	02/10/2020
14	28/09/2020	03/10/2020
15	29/09/2020	04/10/2020
16	30/09/2020	05/10/2020
17	01/10/2020	06/10/2020
18	02/10/2020	07/10/2020
19	03/10/2020	08/10/2020
20	04/10/2020	09/10/2020
21	05/10/2020	10/10/2020
Main

Cell Formulas
Range	Formula
C1	C1	=B1+5
B2:B21	B2	=SEQUENCE(20,,B1+1)
C2:C21	C2	=B2#+5
Dynamic array formulas.

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
C1:C21	Expression	=AND(ISNUMBER(D1),D1>C1)	text	YES
C1:C21	Expression	=AND(ISNUMBER(D1),D1<=C1)	text	YES
C1:C21	Expression	=D1="NA"	text	YES
C1:C21	Expression	=C1<=TODAY()+2	text	NO
C1:C21	Expression	=C1<=TODAY()+3	text	NO
C1:C21	Expression	=C1<=TODAY()+5	text	NO

 

[kristell]

Amazing - thank you so much

 

[Fluff]

You're welcome & thanks for the feedback.

 

[kristell]

I have just realised my reply due date is actually:

=WORKDAY(D6,5,'Bank Holidays'!B4:B10)

D6 being the date received and then the Bank holidays tab has those.

Is there anyway we can do it with these figures? I am sorry - I didn't realise you had put in formula.

 

[Fluff]

If you are referring to the formula in the cells, ignore them, they are just there to give some scrap data.

 

[kristell]

Oh righty ho!! Thanks will give it a try tomorrow - had enough for one day now. Have a good evening.

 

[Fluff]

Cheers