Conditional Formatting

SaraWitch

Board Regular
Joined
Sep 29, 2015
Messages
83
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:

Some videos you may like

Excel Facts

Quick Sum
Select a range of cells. The total appears in bottom right of Excel screen. Right-click total to add Max, Min, Count, Average.

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
56,683
Office Version
  1. 365
Platform
  1. Windows
Can you provide an example in which it is not working correctly?
Namely, what is in columns C, E, T, and V?
And what color are you expecting?
 

SaraWitch

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

To clarify, number 3 is not working - once an approval date is entered I want the cells to either:

a. stay green if approval date is under deadline date;
b. stay orange if approval date is over deadline date.

However, the cells are staying orange if today's date is over the deadline date, regardless of the approval date.

Data is columns as in my original post.

Hope this explains further!

Ta muchly!
Sara
 

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
56,683
Office Version
  1. 365
Platform
  1. Windows
To clarify, number 3 is not working - once an approval date is entered I want the cells to either:
As I said, please give me a specific example, namely what values are in column C, E, T, and V.
I am trying to recreate the situation on my side, so need to know the EXACT values you are working with for one of these that is not working the way you want.
 

SaraWitch

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

ADVERTISEMENT

Hello.

In Column C is the date received.
In Column E is the deadline date.
In Column T is the approval date.
In Column V is the completion date.

What I have is conditional formatting to change the row green if date received is entered, turn orange if the deadline date has passed (today's date) and blue when a completion date is entered. But when an approval date is entered before the deadline date, I want it to be green until completion date is entered, when it turns blue. The line is staying orange if today's date is past the deadline date even though the approval date was before the deadline date.

I have further columns in AB (to show if there is any active data ('A','B' or 'C')), AC (to say 'yes' or 'no' if overdue but no approval yet) and AD (to say 'waiting', 'yes' or 'no' if approval date has not been entered or is over or below deadline date). Formulas as in my initial post. I have tried IFAND formulas based on these cells, i.e. =IF(AND($AC8="A",$AC8="Yes",$AD8="Waiting",$AD8="Yes")) [fill cells orange]. But this isn't working! Maybe I have my formula wrong as I feel this is the way to go!

Any help would be appreciated!

Ta muchly!
 

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
56,683
Office Version
  1. 365
Platform
  1. Windows
Despite my best efforts, I am afraid I am not making myself clear here. I am looking for actual data.

Pick a record, ANY RECORD, that is not working the way you want, and tell me those what those data values in those columns are.

For example, cell C8 is 10/25/2018, etc. (tell me the values in ALL the columns used in your formulas, C, E, T, and V).
Tell me ACTUAL DATA values for any record you have that is not working the way you want.
 

SaraWitch

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

ADVERTISEMENT

Hello.

The actual data can be any dates, but for an example:

Column C = "01/08/2018" (date received) - line turns green [working]
Column E = "12/09/2018" (deadline date) - line turns orange if date is below today's date [working]
Column T = "15/09/2018" (approval date) - line stays orange [working]

but if

Column T = "10/08/2018" (approval date) - line still stays orange, when should be green as the approval date is below the deadline date [not working]

Column V = "Complete" - line turns blue [working]

I have re-jigged the conditional format formula to: =AND($AC8="Yes",$AD8="Waiting",$AD8="Yes",$AB8="A" [fill orange]

with this result:

Column AB = "A" / Column AC = "Yes" / Column AD = "Waiting" or "Yes" - line turns green, not orange [not working]

Hope this now makes sense for you!
 

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
56,683
Office Version
  1. 365
Platform
  1. Windows
Ok, the issue has to do with how your Conditional Formatting rules are written.
=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]

Note that if your record meets multiple Conditions, the order matters. The first one it hits will win out.
So you need to be sure to either:
- make sure that they are placed in the proper order
- write your conditions so that they CANNOT possibly meet more than one condition (use AND to check more values)

So, it is turning orange because it is hitting your second condition here:
Code:
[COLOR=#333333]=AND($AC8="Yes",$AB8="A")[/COLOR]
before it hits the green one.

I would recommend amending it like this:
Code:
=AND($AC8="Yes",$AB8="A",[COLOR=#ff0000]$T8>$E8[/COLOR])
and then it should work the way you want.
 

SaraWitch

Board Regular
Joined
Sep 29, 2015
Messages
83
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
You're AMAZING!!!!

Thank you so, so, so very much, Joe4! No more sleepless nights! :)
 

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
56,683
Office Version
  1. 365
Platform
  1. Windows
You are welcome!

When writing multiple Conditional Formatting rules, I usually try to make them as narrow as possible, so that any record cannot meet more than one condition. That way, I know it won't run into the issue, and order really doesn't matter.
 

Watch MrExcel Video

Forum statistics

Threads
1,127,735
Messages
5,626,583
Members
416,192
Latest member
steinach

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