Conditional Formatting

[SaraWitch]

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!

 

[SaraWitch]

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!

 

[Fluff]

@SaraWitch
In future, when posting the same question on multiple sites, please let us know that you are doing it by providing links to those sites.
See rule 13 here along with the explanation: Forum Rules

https://www.excelforum.com/excel-formulas-and-functions/1250680-conditional-formatting.html

 

[SaraWitch]

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.