Microsoft Office Forums

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #1  
Old 06-26-2018, 02:18 PM
SarahH7 SarahH7 is offline Conditional Formatting Windows 10 Conditional Formatting Office 2016
Novice
Conditional Formatting
 
Join Date: Jun 2018
Posts: 2
SarahH7 is on a distinguished road
Default 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
Reply With Quote
  #2  
Old 06-26-2018, 11:34 PM
ArviLaanemets ArviLaanemets is offline Conditional Formatting Windows 8 Conditional Formatting Office 2016
Expert
 
Join Date: May 2017
Posts: 869
ArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud of
Default

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")
Click <Format>, activate tab Number, select category Custom, and enter into Type field "information provided". <OK>. <OK>;

Create 2nd rule. Enter the formula
Code:
=AND($B2<>"Y";$A2<TODAY()+11)
Click <Format>, activate tab Font, and select yellow as font color. <OK>. <OK>;

Create 3rd rule. Enter the formula
Code:
=AND($B2<>"Y";$A2<TODAY()+6)
Click <Format>, activate tab Font, and select red as font color. <OK>. <OK>;

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>.
Reply With Quote
  #3  
Old 06-27-2018, 07:53 AM
Pecoflyer's Avatar
Pecoflyer Pecoflyer is offline Conditional Formatting Windows 7 64bit Conditional Formatting Office 2010 64bit
Expert
 
Join Date: Nov 2011
Location: Brussels Belgium
Posts: 2,766
Pecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant future
Default

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
Reply With Quote
  #4  
Old 06-27-2018, 09:23 AM
ArviLaanemets ArviLaanemets is offline Conditional Formatting Windows 8 Conditional Formatting Office 2016
Expert
 
Join Date: May 2017
Posts: 869
ArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud of
Default

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.
Reply With Quote
  #5  
Old 06-27-2018, 09:25 AM
Pecoflyer's Avatar
Pecoflyer Pecoflyer is offline Conditional Formatting Windows 7 64bit Conditional Formatting Office 2010 64bit
Expert
 
Join Date: Nov 2011
Location: Brussels Belgium
Posts: 2,766
Pecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant future
Default

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
Reply With Quote
  #6  
Old 06-27-2018, 11:43 AM
SarahH7 SarahH7 is offline Conditional Formatting Windows 10 Conditional Formatting Office 2016
Novice
Conditional Formatting
 
Join Date: Jun 2018
Posts: 2
SarahH7 is on a distinguished road
Default

This worked a treat. Thanks so much for your help.
Reply With Quote
Reply

Thread Tools
Display Modes


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 Conditional formatting marif300 Project 9 09-23-2014 07:23 AM
Conditional Formatting 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

Other Forums: Access Forums

All times are GMT -7. The time now is 04:40 PM.


Powered by vBulletin® Version 3.8.11
Copyright ©2000 - 2024, vBulletin Solutions Inc.
Search Engine Optimisation provided by DragonByte SEO (Lite) - vBulletin Mods & Addons Copyright © 2024 DragonByte Technologies Ltd.
MSOfficeForums.com is not affiliated with Microsoft