Conditional Formatting

SaraWitch

Board Regular
Joined
Sep 29, 2015
Messages
77
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
Hello all.

I am trying to conditional format for four outcomes:

1. Change cells to green if active
2. Change cells to orange if today's date is over deadline date and not yet approved
3. Change cells to orange if approval date is over deadline date, but green if under deadline date
4. Change cells to blue if complete

The formulas I have entered are all working except number 3 (cells stay orange if today's date is over deadline date regardless if approval date is under)!

Spreadsheet details are:

Column C: Date received
Column E: Deadline date
Column T: Approval date
Column V: Complete/Cancelled/Refused

I then have three additional columns that show:

Column AB (Status): A [Active], B [Complete], C [Blank] (=IF$C8="","C",(IF($V8>0,"B",IF($V8="","A"))))
Column AC (Overdue (no approval yet)): Yes, No (=IF($E8>TODAY(),"No","Yes")
Column AD (Approval overdue): Waiting, Yes, No (=IF($T8="","Waiting",(IF($T8>$E8,"Yes","No")))

I have conditional formatted rows as (order as seen on CF Rules Manager box):

=AND($AC8="Yes",$AD8="Yes",$AB8="A") [format fill orange]
=AND($AC8="Yes",$AB8="A") [format fill orange]
=$V8>0 [format fill blue]
=$C8>0 [format fill green]

I know it makes a difference what order the CFs are and I have played with them to no avail (have experimented with 'Stop If True' box too, which makes no difference!).

Any help would be greatly appreciated as this is driving me mad!

Ta muchly, folks!
 
Last edited:

SaraWitch

Board Regular
Joined
Sep 29, 2015
Messages
77
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
You've been really helpful and what a useful tip to remember in future! I was clearly trying to over-complicate it and then got stuck down a certain mind-track!

Thank you once again, Joe4!
 

Some videos you may like

Excel Facts

Create a chart in one keystroke
Select the data and press Alt+F1 to insert a default chart. You can change the default chart to any chart type

SaraWitch

Board Regular
Joined
Sep 29, 2015
Messages
77
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
I do apologise.


In my haste to find an answer, I did not read all the rules properly. I will, of course, in future adhere to them.


Once again, please accept my apologies.
 

Watch MrExcel Video

Forum statistics

Threads
1,127,256
Messages
5,623,680
Members
415,983
Latest member
MusicMan

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
Top