Multiple conditional formatting - or similar?

kristell

New Member
Joined
Dec 12, 2014
Messages
18
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.
 

Some videos you may like

Excel Facts

Save Often
If you start asking yourself if now is a good time to save your Excel workbook, the answer is Yes

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
48,287
Office Version
  1. 365
Platform
  1. Windows
How about
+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
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
48,287
Office Version
  1. 365
Platform
  1. Windows
You're welcome & thanks for the feedback.
 

kristell

New Member
Joined
Dec 12, 2014
Messages
18

ADVERTISEMENT

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
Joined
Jun 12, 2014
Messages
48,287
Office Version
  1. 365
Platform
  1. Windows
If you are referring to the formula in the cells, ignore them, they are just there to give some scrap data.
 

kristell

New Member
Joined
Dec 12, 2014
Messages
18
Oh righty ho!! Thanks will give it a try tomorrow - had enough for one day now. Have a good evening.
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
48,287
Office Version
  1. 365
Platform
  1. Windows

Watch MrExcel Video

Forum statistics

Threads
1,114,314
Messages
5,547,174
Members
410,775
Latest member
alal1030
Top