#1
|
|||
|
|||
Conditional Formatting
I have a column of dates (column A) which i have conditional formatted to turn red if within 5 days from now, yellow if within 10 days & normal if over 10 days from now. The dates are for information to be submitted to me so I can easily see which rows are approaching the deadline. Once information is provided I then want another column (B), in which I plan to put a ‘Y’ when the information is received. When I put this ‘Y’ in, I would like column A to change from a date to say ‘information provided’. Is this possible? Thanks
|
#2
|
|||
|
|||
Let's assume your data start from row 2 (row 1 is for column headers). Select the datarange in column A and activate Conditiona Formatting;
Create a 1st rule. Select "Use a formula to determine which cells to format" (this selection goes for all rules). Enter the formula Code:
=($B2="Y") Create 2nd rule. Enter the formula Code:
=AND($B2<>"Y";$A2<TODAY()+11) Create 3rd rule. Enter the formula Code:
=AND($B2<>"Y";$A2<TODAY()+6) Check rules order (from top down: red, yellow, text replaced), and that Stop if True is unchecked for all rules. When different, then make needed changes; Click <Apply>. |
#3
|
||||
|
||||
I haven't checked Arvi's solutions but don't forget to eventually change the semi colons to commas depending on your regional settings
__________________
Did you know you can thank someone who helped you? Click on the tiny scale in the right upper hand corner of your helper's post |
#4
|
|||
|
|||
I usually remember to use commas instead (when I write the formula directly into post), but this time I tested formulas, and then copied them into post.
|
#5
|
||||
|
||||
Well, as the OP didn't indicate his/her location....
__________________
Did you know you can thank someone who helped you? Click on the tiny scale in the right upper hand corner of your helper's post |
#6
|
|||
|
|||
This worked a treat. Thanks so much for your help.
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Looking for Conditional Formatting help! | clayg | Excel | 0 | 10-12-2017 07:41 PM |
Conditional Formatting | chrisoconn | Excel | 0 | 07-05-2017 02:10 AM |
Conditional formatting | marif300 | Project | 9 | 09-23-2014 07:23 AM |
Conditional formatting that ignores other formatting rules | info_guy2 | Excel | 1 | 07-03-2014 10:07 AM |
Conditional formatting | zanat0s | Excel | 4 | 01-20-2012 03:30 AM |