+ Reply to Thread
Results 1 to 4 of 4

Conditionally format based on identical column values, provided adjacent cells meet cond.

  1. #1
    Registered User
    Join Date
    06-28-2018
    Location
    Indianapolis, Indiana
    MS-Off Ver
    2010
    Posts
    2

    Conditionally format based on identical column values, provided adjacent cells meet cond.

    I have a very large set of data. I need to automatically highlight identical sets of cells provided that each corresponding cell to the left is greater than a certain number.
    For example, below if every asset test date for a given work order number is greater than 12/31/2018 then the entire 'set' of work order numbers gets highlighted, else none of them get highlighted.

    So, for below the first row work order number 20409863 , NONE of the 20409863 cells get highlighted because just one test date doesn't meet the requirement.
    20410401 gets highlighted because it's date is greater than 12/31/2018 and there's just one asset for this particular w/o number.
    20410486 is same situation as the first example, so none of the cells with value 20410486 get highlighted.
    26817574 - every adjacent cell next test date is greater than the requirement, so every cell value 26817574 will be highlighted (green color)

    6/22/2017 20409863
    1/19/2021 20409863
    1/19/2021 20409863
    1/11/2027 20410401
    12/20/2025 20410486
    2/5/2017 20410486
    11/9/2020 20410486
    5/7/2026 26817574
    5/7/2026 26817574
    12/7/2026 26817574

  2. #2
    Forum Expert bebo021999's Avatar
    Join Date
    07-22-2011
    Location
    Vietnam
    MS-Off Ver
    Excel 2016
    Posts
    9,409

    Re: Conditionally format based on identical column values, provided adjacent cells meet co

    Assuming date is from A1, value is from B1
    Try CF formula for B1 then copy/paste format down:
    Please Login or Register  to view this content.
    Quang PT

  3. #3
    Registered User
    Join Date
    06-28-2018
    Location
    Indianapolis, Indiana
    MS-Off Ver
    2010
    Posts
    2

    Re: Conditionally format based on identical column values, provided adjacent cells meet co

    Thanks ! it hasn't worked for me yet, maybe I'm doing something wrong. Re-thinking it, I should probably skip the formatting deal and just choose an empty cell to the right side and return a value in text like 'close' if it's true. 'open' if it's false

    I think I understand most of your formula except the 10^10, what is that ?

  4. #4
    Forum Expert bebo021999's Avatar
    Join Date
    07-22-2011
    Location
    Vietnam
    MS-Off Ver
    Excel 2016
    Posts
    9,409

    Re: Conditionally format based on identical column values, provided adjacent cells meet co

    10^10 is number 1 following by 10 zero, meaning very big value, to make sure bigger than 12/31/2018

+ 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. Conditionally format adjacent Cells - if less than zero
    By rhiakatkat in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 01-26-2018, 03:31 PM
  2. Replies: 10
    Last Post: 10-07-2013, 11:29 AM
  3. Replies: 1
    Last Post: 03-02-2013, 05:06 AM
  4. count cells if ADJACENT row values meet criteria
    By xinutel in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 11-30-2012, 03:55 PM
  5. Replies: 23
    Last Post: 09-25-2009, 07:39 PM
  6. Replies: 1
    Last Post: 09-19-2009, 08:09 PM
  7. Format cell based on adjacent cells values
    By the majestic ferny in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 12-16-2005, 02:35 PM

Tags for this Thread

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