Microsoft Office Forums

Go Back   Microsoft Office Forums > Microsoft Excel > Excel

Reply
 
LinkBack Thread Tools Display Modes
  #1  
Old 06-26-2018, 02:18 PM
SarahH7 SarahH7 is offline Windows 10 Office 2016
Novice
 
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 Windows 8 Office 2016
Expert
 
Join Date: May 2017
Posts: 348
ArviLaanemets is on a distinguished road
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 Windows 7 64bit Office 2010 64bit
Moderator
 
Join Date: Nov 2011
Location: Brussels Belgium
Posts: 2,253
Pecoflyer is a glorious beacon of lightPecoflyer is a glorious beacon of lightPecoflyer is a glorious beacon of lightPecoflyer is a glorious beacon of lightPecoflyer is a glorious beacon of light
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
__________________
Problem solved ? Let others know by clicking " Thread Tools" then " Mark thread as solved".( This can be undone if need be)
Want to thank for the help received ? Click the scales symbol in the upper right corner of a post from the person you want to thank.
Reply With Quote
  #4  
Old 06-27-2018, 09:23 AM
ArviLaanemets ArviLaanemets is offline Windows 8 Office 2016
Expert
 
Join Date: May 2017
Posts: 348
ArviLaanemets is on a distinguished road
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 Windows 7 64bit Office 2010 64bit
Moderator
 
Join Date: Nov 2011
Location: Brussels Belgium
Posts: 2,253
Pecoflyer is a glorious beacon of lightPecoflyer is a glorious beacon of lightPecoflyer is a glorious beacon of lightPecoflyer is a glorious beacon of lightPecoflyer is a glorious beacon of light
Default

Well, as the OP didn't indicate his/her location....
__________________
Problem solved ? Let others know by clicking " Thread Tools" then " Mark thread as solved".( This can be undone if need be)
Want to thank for the help received ? Click the scales symbol in the upper right corner of a post from the person you want to thank.
Reply With Quote
  #6  
Old 06-27-2018, 11:43 AM
SarahH7 SarahH7 is offline Windows 10 Office 2016
Novice
 
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 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


All times are GMT -7. The time now is 08:30 PM.


Powered by vBulletin® Version 3.8.1
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
SEO by vBSEO ©2011, Crawlability, Inc.
MSOfficeForums.com is not affiliated with Microsoft