+ Reply to Thread
Results 1 to 3 of 3

Advance Conditional Formatting

  1. #1
    Registered User
    Join Date
    09-09-2011
    Location
    kuching, Malaysia
    MS-Off Ver
    Excel 2007
    Posts
    52

    Lightbulb Advance Conditional Formatting

    Hi All,

    I have an excel file with conditional formatting which i get it done with this forum help. I would like it to be in more advance mode now. So i am not sure should i open new thread or use back the previous thread for this request. Please correct me if i am wrong.

    Anyway, I will explain what i need. In the excel file i have material expire date. The rule applys to the date is first expire first out. Refer to the excel file "Column D". It will highlight "green" for the next date going to expire.

    Now my question is if i have stock out the highlighted materials, i want it to highlight next nearest expire date. Example "row 7 & 8" i have stocked out the materials. Column D should change the color to "pink" for the materials that already stocked out and highlight the next expire date in green, in this case will be "row 10 - 13"

    How should i do this?
    Attached Files Attached Files
    Last edited by nickh1981; 11-08-2013 at 03:35 AM.

  2. #2
    Forum Expert JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

    Re: Advance Conditional Formatting

    1) Delete the existing rules
    2) Add these rules so that when done they appear IN THIS ORDER, be sure to click STOP IF TRUE on the first two:

    PINK: =K3>=H3 (stop if true)
    RED: =D3<TODAY() (stop if true)
    GREEN: =$D3=MIN(IF(($B3=$B$3:$B$81)*($D$3:$D$81>=TODAY())*($H$3:$H$81>$K$3:$K$81),$D$3:$D$81))

    http://screencast.com/t/nfRoymtX
    _________________
    Microsoft MVP 2010 - Excel
    Visit: Jerry Beaucaire's Excel Files & Macros

    If you've been given good help, use the icon below to give reputation feedback, it is appreciated.
    Always put your code between code tags. [CODE] your code here [/CODE]

    ?None of us is as good as all of us? - Ray Kroc
    ?Actually, I *am* a rocket scientist.? - JB (little ones count!)

  3. #3
    Registered User
    Join Date
    09-09-2011
    Location
    kuching, Malaysia
    MS-Off Ver
    Excel 2007
    Posts
    52

    Re: Advance Conditional Formatting

    Hi JB,

    Thanks for the fast response. It works exactly the way i want.

+ 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 sorting and make a list using advance filter
    By mukeshbaviskar in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 05-12-2013, 12:31 PM
  2. Conditional Format or Advance Filter
    By alidde in forum Excel General
    Replies: 2
    Last Post: 08-11-2012, 06:15 PM
  3. Advance filter criteria basis cell formatting
    By raghavfastest in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 05-13-2010, 03:59 AM
  4. Advance Conditional Formatting
    By tforbes in forum Excel General
    Replies: 1
    Last Post: 11-22-2007, 12:35 PM
  5. Advance conditional formatting
    By jgclickfind in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 03-16-2007, 10:16 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