Conditional Formatting Based on Dates-3 Columns [Closed]

Report
Posts
6
Registration date
Wednesday September 4, 2013
Status
Member
Last seen
September 18, 2013
-
Posts
2776
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
July 26, 2021
-
Dear brothers/sisters,
I need your help to do this below facilities in excel. I explain it, then you please guide me well in detail.

1) In Cells A5:A100 I am entering a date manually. These cells should be in Green for the next 10 days from the date entry and then to Red when it is later than 10 days. Comparison is between my manual dates and the computer's automatic calendar date of the day.

2) During Green or Red in above (A5:A100), I am entering another date in column B5:B100. If entered a date in column B, then the column A should clear the colours, means not anymore Green or Red.

Then like cells A5:A100, cells (B5:B100) also should be coloured in Red for expiry and Green for Valid for the 10 days gap back and forward comparing with computer system's auto date.

3) Like cells B5:B100, followed by cells A5A100, I need another column C from C5:C100. I will enter here only numbers in some digits. During entry in cells C5:C100, no Green or Red will be there in cells B.
That's all my requirements. Please gentlemen help me in detail.

Thank you.

Hakeem.

4 replies

Posts
6
Registration date
Wednesday September 4, 2013
Status
Member
Last seen
September 18, 2013

Hello experts, No one guided me till? Pls. help.
Posts
2776
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
July 26, 2021
465
Hi Hakeem,

Cell A5 conditional format formula's:
=AND(A5-TODAY()>10,B5="") --> Red
=AND(A5-TODAY()>=0,A5-VANDAAG()<=10,B5="") --> Green

Drag A5 down to A100 applying format only (after dragging a small square should appear with that option).
Then drag range A5:A100 one column to the right, again applying format only.

If dragging doesn't work or suit you, you can also copy and paste special and then select Format.

Best regards,
Trowa
Posts
6
Registration date
Wednesday September 4, 2013
Status
Member
Last seen
September 18, 2013

Dear Trowa,
Thank you and I appreciate for helping but regret to say it is not meeting the requirement.
I believe you can achieve the target further as you began already.
please note the below and advise me the correct.
I went through conditional formatting>New rule>format only cell that contain>cell value>equal to>(your formula with filled colour)
The cells are coloured when the cells are blank only. While entering a date, there becomes no colour either Green or Red.

Please try again as it is a challenge for the knowledge.

Waiting for your reply.
Posts
2776
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
July 26, 2021
465
Hi Hakeem,

Excel 2003 is the version I'm using, so I can't say for sure which path to follow.

You say: New rule>format only cell that contain>cell value>equal to>
But we are not looking for a cell value that is equal to something.

I think you have to use: Use a formula to determine which cell to format

I would suggest to play around with the CF options or to post another question asking how to use a formula together with CF in Excel 2010 (or later don't know which version you are using.

One last think we can try is for me to upload a file with CF. When you open the file, then hopefully the CF still works correctly and you can see how it is done.
Here is the file:
http://speedy.sh/p6jMX/Hakeem-CF-based-on-dates.xls

Curiously awaiting your reply to see how this worked out for you.

Best regards,
Trowa
Posts
6
Registration date
Wednesday September 4, 2013
Status
Member
Last seen
September 18, 2013

Dear Trowa,
Thank you for the reply. Unfortunately, the file is unable to be downloaded into my system as not supporting the system (OS:2013/64 bit) will it be possible to my personal address?
waiting for your reply.pls.
Posts
2776
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
July 26, 2021
465
Hi Hakeem,

Strange that you are not offered to convert the file. I will send you a different version this weekend since I don't think sending the same file to your e-mail will be useful.

Best regards,
Trowa
Posts
2776
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
July 26, 2021
465
Ok Hakeem, this is from Excel 2007. Hopefully it helps.

http://speedy.sh/EcW99/Hakeem-CF-based-on-dates.xlsx
Dear Trowa,
Again you have sent the file through unsupportable way to my computer. I downloaded and installed but when I opened the logo/software it said it can not be supported. Kindly send to my email<Hakeembeautyatgmail>

Thank you.
Hakeem,
Posts
2776
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
July 26, 2021
465
Hi Hakeem,

Not sure what makes you think that retrieving the file in a different way will make your computer handle the file differently, but I will give it a try.

Best regards,
Trowa

Subscribe To Our Newsletter!

The Best of CCM in Your Inbox

Subscribe To Our Newsletter!