Conditional Formatting question.

HerrUther

New Member
Joined
Sep 3, 2014
Messages
7
Hello all,

I'm trying to create a spreadsheet to track assessments on personnel.

I've created a list of columns that use the formula =IF(E46="","",DATE(YEAR(E46),MONTH(E46)+12,DAY(E46))) to auto calculate the next date for assessment this one is for the 12 monthly reviews. I've also added conditional formatting that changes the date to orange 60 days before expiry and red once expired.

These dates continue to run and what I'm trying to achieve is a some kind of conditional formatting or something else of something that changes the expired date colour to 'No formatting' once the next date field is completed.

Sorry for the lack of pictures...

I'm using excel 2010 and I'm a very basic user so please keep it simple :p
 

Excel Facts

VLOOKUP to Left?
Use =VLOOKUP(A2,CHOOSE({1,2},$Z$1:$Z$99,$Y$1:$Y$99),2,False) to lookup Y values to left of Z values.
Hi and Welcome to MrExcel,

You can add a criteria to each Conditional Formatting (CF) Rule that tests if the "next date field is completed".

It's hard to suggest a specific formula without knowing what your existing CF Rules are, and to which cells those apply, and what it means to complete a date field.

As an example, if your <60 days =Orange Rule for Cell E48 was:
=TODAY()>E48-60

and you are testing if F48 has a value or is blank to indicate the next date field is completed, you could modify the CF Rule to....
=AND(TODAY()>E48-60,LEN(F48)>0)
 
Upvote 0
Hi Jerry,

Thanks for your reply.

My CF is 'Format only cells that contain' 'cell value' 'less than' '=NOW()' for red and '=NOW()+60' for Orange. This is applied to multiple cells in multiple rows =$D$3:$D$70, I think this is my issue... I'm I asking too much of the CF maybe?

I would like the red field to be cancelled by the next date in the next cell once completed, don't know if it's possible.

I tired =AND(NOW(),LEN($E$3:$E$70)>0) but all it did was make all cells red even without data...
 
Upvote 0
Instead of using ''Format only cells that contain', use Rule Type:
'Use a formula to determine which cells to format'

Remove all your existing CF rules in the sheet, select cell D3, then make a rule for Red:

Format values where this formula is true:
=AND(D3< NOW(),LEN(E3)=0)

Applies to: $D$3:$D$70
 
Upvote 0
Thanks for your support and understanding of my limited exposure to excel...

So if I create another rule for Orange I'd just use:

Format values where this formula is true:
=AND(D3< NOW()+60,LEN(E3)=0) Applies to: $D$3:$D$70??

and then ensure that the is red and then orange in rules manager??

Genius... I knew there was a reason to love excel :)

 
Upvote 0

Forum statistics

Threads
1,213,549
Messages
6,114,261
Members
448,558
Latest member
aivin

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