# Multiple conditional formatting - or similar?

#### kristell

##### New Member
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.

### Excel Facts

Why does 9 mean SUM in SUBTOTAL?
It is because Sum is the 9th alphabetically in Average, Count, CountA, Max, Min, Product, StDev.S, StDev.P, Sum, VAR.S, VAR.P.

#### Fluff

##### MrExcel MVP, Moderator
+Fluff New.xlsm
BCD
115/09/202020/09/2020na
216/09/202021/09/202018/09/2020
317/09/202022/09/202021/09/2020
418/09/202023/09/202026/09/2020
519/09/202024/09/202026/09/2020
620/09/202025/09/202024/09/2020
721/09/202026/09/2020na
822/09/202027/09/2020
923/09/202028/09/2020
1024/09/202029/09/2020
1125/09/202030/09/2020
1226/09/202001/10/2020
1327/09/202002/10/2020
1428/09/202003/10/2020
1529/09/202004/10/2020
1630/09/202005/10/2020
1701/10/202006/10/2020
1802/10/202007/10/2020
1903/10/202008/10/2020
2004/10/202009/10/2020
2105/10/202010/10/2020
Main
Cell Formulas
RangeFormula
C1C1=B1+5
B2:B21B2=SEQUENCE(20,,B1+1)
C2:C21C2=B2#+5
Dynamic array formulas.
Cells with Conditional Formatting
CellConditionCell FormatStop If True
C1:C21Expression=AND(ISNUMBER(D1),D1>C1)textYES
C1:C21Expression=AND(ISNUMBER(D1),D1<=C1)textYES
C1:C21Expression=D1="NA"textYES
C1:C21Expression=C1<=TODAY()+2textNO
C1:C21Expression=C1<=TODAY()+3textNO
C1:C21Expression=C1<=TODAY()+5textNO

#### kristell

##### New Member
Amazing - thank you so much

#### Fluff

##### MrExcel MVP, Moderator
You're welcome & thanks for the feedback.

#### kristell

##### New Member

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

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

#### kristell

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

Cheers

Replies
3
Views
83
Replies
3
Views
55
Replies
9
Views
100
Replies
3
Views
48
Replies
5
Views
150