+ Reply to Thread
Results 1 to 4 of 4

Conditional Formatting

  1. #1
    Registered User
    Join Date
    10-25-2018
    Location
    South east England
    MS-Off Ver
    Microsoft Windows 10
    Posts
    15

    Conditional Formatting

    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!).

    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 then rejigged the conditional format formula to: =AND($AC8="Yes",$AD8="Waiting",$AD8="Yes",$AB8="A" [format fill orange]

    with this result:

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

    Maybe I have my formula wrong, but instinct is telling me this is the way to go!

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

    Ta muchly, folks!

  2. #2
    Registered User
    Join Date
    10-25-2018
    Location
    South east England
    MS-Off Ver
    Microsoft Windows 10
    Posts
    15

    Re: Conditional Formatting

    The lovely Joe4 from another forum has solved this issue for me!

    Answer is that the formula was wrong - Joe4's suggestion of =AND($AC8="Yes",$AB8="A",$T8>$E8) worked perfectly!

  3. #3
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,369

    Re: Conditional Formatting

    SaraWitch - please see this forum's rules on cross-posting. You are required to tell us where else you have asked the same question when you initially post your query - you did not do that. Please ensure that you do so if you cross-post in future. The rule is the same on most forums. Thanks.

    If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.
    Ali


    Enthusiastic self-taught user of MS Excel who's always learning!
    Don't forget to say "thank you" in your thread to anyone who has offered you help.
    You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.

    Forum Rules (updated August 2023): please read them here.

  4. #4
    Registered User
    Join Date
    10-25-2018
    Location
    South east England
    MS-Off Ver
    Microsoft Windows 10
    Posts
    15

    Re: Conditional Formatting

    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.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] Override conditional formatting (in general, without changing the conditional formatting)
    By Stormin' in forum Excel Programming / VBA / Macros
    Replies: 9
    Last Post: 11-21-2017, 07:15 AM
  2. Replies: 1
    Last Post: 12-08-2016, 03:14 PM
  3. Conditional Formatting Removing Previous Conditional Formatting?
    By CravingGod in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 02-04-2016, 01:02 PM
  4. Replies: 6
    Last Post: 01-08-2016, 06:44 PM
  5. Opening xlsm files with conditional formatting opens with removed conditional formatting
    By Martijn.Steenbakker in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 03-07-2014, 05:38 AM
  6. Replies: 1
    Last Post: 09-20-2013, 06:23 PM
  7. Replies: 3
    Last Post: 05-15-2012, 04:13 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1