Conditional Formatting Adjacent Cells

lulaB

New Member
Joined
Dec 6, 2017
Messages
6
So i have my initial formats done:

Column I: Planned date
Column J: Revised date (if needed)
Column K: Actual Date

Both I and J have a format to turn colors based on how far we are for the dates.. (ex: 5/15/30 days)

My issue, if there has been a revised date in J, i'd like I to turn white- and once an ACTUAL date posted in K I'd like for J (or original date I) to turn white. SO the only dates colored are past due and up coming (ie not completed) I am stuck.. help?
 

Excel Facts

Why does 9 mean SUM in SUBTOTAL?
It is because Sum is the 9th alphabetically in Average, Count, CountA, Max, Min, Product, StDev.S, StDev.P, Sum, VAR.S, VAR.P.
Maybe this will help with what I'm talking about

A B C

10/16/2017
12/1/2017
7/28/2017 10/31/2017
9/1/2017 12/1/2017

1/26/2018 3/7/2018
2/2/2018 3/21/2018
9/22/2017 1/17/2018
11/17/2017 1/24/2018
7/28/2017 10/31/2017 10/31/2017
9/1/2017 12/5/2017


Need Column A to turn white if a date is in the column B, and if date is in column C – Column A and B should be white
 
Upvote 0
So, are you just looking to check to see if both columns B and C are populated with dates?
If so, just use a Conditional Formatting formula like this:
Code:
=AND(B2>0,C2>0)
 
Upvote 0
no, So column A is "Planned date" and B is "updated date" so if the date has been updated from the planned date (A) i want the planned date to turn white so you only focus on the color in the 'updated date" or column B
 
Upvote 0
Sorry, I am afraid your explanations are no very clear.
Let's walk through each situation, and you can tell us what should happen in each situation.

1. Date entered in column A only
2. Dates entered in column A and column B only
3. Dates entered in columns A, B, and C
 
Upvote 0
dates A- Original planned delivery date

Date B- UPDATED delivery date

Date C- Delivered date
A PlannedB updateC delivered
12/1/2017

7/28/20173/31/2018
7/28/20179/20/201710/1/2017
1/26/2018


<tbody>
</tbody>

I currently have the CF that changes the color of the boxes as i get withing a week..month..3 months of the date.
 
Upvote 0
You still haven't answered my question.
Remember, while this problem is very familiar to you, all that we have to go on is the little that you have posted here.
You haven't even posted your current rules.

So based on the example that you posted in your last post, walk us through each instance, and let us know what you want to happen in each one (and the logic behind it).
 
Upvote 0
OH! I am sorry!

Column A is just dates and the CF changes the color based on how close we are getting to the due date or if we are past the date ( graded color scale applies to =$A:$A.... Formula Min=today()+15 (RED); Mid =Today()+30; Max =Today()+60)

Column B- the updated date, same CF- turns green-yellow-red based on distance to the date or past date. ( graded color scale applies to =$A:$A.... Formula Min=today()+15 (RED); Mid =Today()+30; Max =Today()+60)
IF there is an updated date in B- THEN column A turns white and the CF in B is colored

Column C- item delivered Formula =$C:$C <> format- Bold green text)


What i am trying to do...

IF there is an updated date in B- THEN column A turns white and the CF in B is colored

ONCE the actual date is filled in both column A and B are white.


the reason- I want to make it 'clean' meaning your eye is drawn to the most active date in that row- whether that be A (only date) B (the updated date) or C (the delivered date).



does that help? Im sorry about not being clear before.
 
Upvote 0
OK, I think you will need to change over how you are doing Conditional Formatting, and use the formula options for all.
So, you would probably use a lot of ANDs to make sure they meet all your criteria.
For example, to check to see if the value in A2 is within the past 15 days, and columns B and C are blank, the CF formula would look like:
Code:
=AND(B2="",C2="",A2>(TODAY()-15),A2 < TODAY())
<today())[ code]<="" html=""></today())[>
 
Last edited:
Upvote 0

Forum statistics

Threads
1,213,526
Messages
6,114,136
Members
448,551
Latest member
Sienna de Souza

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top