+ Reply to Thread
Results 1 to 9 of 9

Conditional Formatting based on 4 criteria

  1. #1
    Forum Contributor
    Join Date
    08-15-2006
    Location
    USA
    MS-Off Ver
    Office 365, 64 bit
    Posts
    362

    Conditional Formatting based on 4 criteria

    The attached file has 2 columns that I want formatted based on detailed criteria I outlined in the file. I currently have 2 basic formulas, but these are confusing since I'm unable to create the correct formulas. Please see attached file... Thx
    Attached Files Attached Files

  2. #2
    Forum Guru Bo_Ry's Avatar
    Join Date
    09-10-2018
    Location
    Thailand
    MS-Off Ver
    MS 365
    Posts
    7,211

    Re: Conditional Formatting based on 4 criteria

    Please try CF formula applies to N7:O126

    Fill Green
    =AND($M7="In Progress",N7< TODAY(),INDEX($CW7:$DA7,,COLUMNS($N7:N7)*4-3)<>"")

    Fill Yellow
    =AND($M7="In Progress",N7=TODAY(),INDEX($CW7:$DA7,,COLUMNS($N7:N7)*4-3)="")

    Fill Red
    =AND($M7="In Progress",N7< TODAY(),INDEX($CW7:$DA7,,COLUMNS($N7:N7)*4-3)="")

  3. #3
    Forum Contributor
    Join Date
    08-15-2006
    Location
    USA
    MS-Off Ver
    Office 365, 64 bit
    Posts
    362

    Re: Conditional Formatting based on 4 criteria

    Hi -

    The location of the columns changed recently and now the formulas provided before are not working. I've tried changing the formula a bit but is still not working. Any ideas? I've attached an updated file to show the new location of the columns. Thx
    Attached Files Attached Files

  4. #4
    Forum Contributor
    Join Date
    08-15-2006
    Location
    USA
    MS-Off Ver
    Office 365, 64 bit
    Posts
    362

    Re: Conditional Formatting based on 4 criteria

    I did modify the formulas that Bo_Ry provided and worked back in Dec-2018 with the new columns. I also try to adjust the *4-3 but to no avail. Am I missing something? Thx

  5. #5
    Forum Contributor
    Join Date
    08-15-2006
    Location
    USA
    MS-Off Ver
    Office 365, 64 bit
    Posts
    362

    Re: Conditional Formatting based on 4 criteria

    This has been solved. Another formula in the existing columns was causing the issue. It was resulting 1/0/00 which in turn caused the conditional formula not to work. Thx

  6. #6
    Forum Contributor
    Join Date
    08-15-2006
    Location
    USA
    MS-Off Ver
    Office 365, 64 bit
    Posts
    362

    Re: Conditional Formatting based on 4 criteria

    I ran into a snag with the existing conditional formula I'm using in columns P and Q. The formula will highlight the date as red if the target date has not been met and is past due from columns FK:FL and FO:FP. However, columns P & Q now bring in dates from BN:BO and FO:FP for the Agile Workflow paths (column G). It highlights the date as past due when it has been met (see row 45). I've attached a sample file.
    Attached Files Attached Files

  7. #7
    Forum Guru Bo_Ry's Avatar
    Join Date
    09-10-2018
    Location
    Thailand
    MS-Off Ver
    MS 365
    Posts
    7,211

    Re: Conditional Formatting based on 4 criteria

    Current formula
    =AND($O45="In Progress",P45< TODAY(),INDEX($FL45:$FP45,,COLUMNS($P45:P45)*4-3)="")
    Check All below condition must be True to highlight.
    1. $O45="In Progress"
    2. P45< TODAY()
    3. FL =""

    Please walk me through step by step
    Check column P with FK, FL, FO, FP, BN, BO for =, > or < or what?

  8. #8
    Forum Contributor
    Join Date
    08-15-2006
    Location
    USA
    MS-Off Ver
    Office 365, 64 bit
    Posts
    362

    Re: Conditional Formatting based on 4 criteria

    Current formula
    =AND($O45="In Progress",P45< TODAY(),INDEX($FL45:$FP45,,COLUMNS($P45:P45)*4-3)="")
    Check All below condition must be True to highlight.
    1. $O45="In Progress"
    2. P45< TODAY()
    3. FL =""

    Please walk me through step by step
    Check column P with FK, FL, FO, FP, BN, BO for =, > or < or what?

    My Comment:
    The current formula is looking at the FL and FP columns to see if the milestone date has been met. If the "Actual" date is populated, than met. If the "Actual" date is NOT populated and the "Target" date is already past due (TODAY), than NOT met. That's when the date in columns P or Q are highlighted red to quickly indicate at a glance w/o having to scroll right that the milestone is past due.

    The issue is that the Agile Workflow milestones are not updated on FL and FP columns, these are on BM and BO... therefore, the current formula is not working for these...

  9. #9
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2019
    Posts
    17,425

    Re: Conditional Formatting based on 4 criteria

    Perhaps the following will help:
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    Let us know if you have any questions.
    Consider taking the time to add to the reputation of everybody that has taken the time to respond to your query.

+ 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] Conditional Formatting based on 3 criteria
    By rz6657 in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 01-15-2018, 06:41 PM
  2. [SOLVED] Conditional formatting based on criteria
    By fscutaro in forum Excel General
    Replies: 7
    Last Post: 12-08-2015, 03:31 PM
  3. [SOLVED] Conditional Formatting - Based on Criteria
    By mlbdc2012 in forum Excel General
    Replies: 2
    Last Post: 02-03-2015, 05:50 PM
  4. [SOLVED] Conditional Formatting Based on Two Criteria (Using VBA)
    By macrorookie in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 04-07-2014, 07:19 PM
  5. [SOLVED] Conditional formatting based on two criteria
    By Brumbot in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 01-24-2013, 05:44 AM
  6. [SOLVED] Conditional Formatting based on 2 criteria
    By SantosJ in forum Excel General
    Replies: 2
    Last Post: 05-23-2012, 05:11 AM
  7. Conditional Formatting based on 2 Criteria
    By SteelDog in forum Excel General
    Replies: 3
    Last Post: 06-23-2008, 12:58 AM

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