+ Reply to Thread
Results 1 to 17 of 17

2 rules for conditional formatting

  1. #1
    Forum Contributor
    Join Date
    09-04-2013
    Location
    London
    MS-Off Ver
    Office 365
    Posts
    117

    2 rules for conditional formatting

    Hi im new so go easy.

    Ok so im building a workbook and i have a sheet that records sales/invoices. I insert date using a pop-up calendar, the payment terms in days is determined via VLOOKUP depending on which customer is selected from drop down, fine no problem. My payment due date cell simply adds the date ive inserted to the payment terms day cell and returns a due date eg 04-sep +10 days returns 14-sep to that cell, simple enough. This cell however i have conditionally formatted to display red if this cell is less than =TODAY(). again fine no problem. this shows an easy to see customer whos payment is overdue. a few columns along i have a Payment status column with a drop down with "Paid" & "Unpaid". I want to be able to overide the previously mentioned conditional formatting once that transaction has been marked "Paid" and this is where im stuck?

    Any ideas would be greatly appreciated.

    Thanks

    Ross
    Last edited by alfgrey; 09-04-2013 at 10:58 AM.

  2. #2
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    44,087

    Re: 2 rules for conditional formatting

    You can use AND to have more than one condition.

    Post a sample workbook.

    Regards, TMS
    Trevor Shuttleworth - Retired Excel/VBA Consultant

    I dream of a better world where chickens can cross the road without having their motives questioned

    'Being unapologetic means never having to say you're sorry' John Cooper Clarke


  3. #3
    Forum Contributor
    Join Date
    09-04-2013
    Location
    London
    MS-Off Ver
    Office 365
    Posts
    117

    Re: 2 rules for conditional formatting

    thanks for your reply, im still not sure how to express this. ive tried =AND(TODAY()<,K2="Unpaid") but this doesnt work.

  4. #4
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    44,087

    Re: 2 rules for conditional formatting

    The reason for asking for a sample workbook is so we know which column(s) you need formatting and which columns have the conditional values ... and to be able to test any proposed solution.

    If the date to be tested is in column X, let's say, the formula would look like:

    Formula: copy to clipboard
    Please Login or Register  to view this content.



    Regards, TMS

  5. #5
    Forum Contributor
    Join Date
    09-04-2013
    Location
    London
    MS-Off Ver
    Office 365
    Posts
    117

    Re: 2 rules for conditional formatting

    ForumWB.xlsx

    Couldnt get that to work either must be missing something

  6. #6
    Forum Guru Jonmo1's Avatar
    Join Date
    03-08-2013
    Location
    Bryan, TX
    MS-Off Ver
    Excel 2010
    Posts
    9,763

    Re: 2 rules for conditional formatting

    here's the formula you have in K2 of the book you just posted
    =AND(TODAY()<"K2",K2="Unpaid")

    There's a couple problems.
    1. The Quotes around K2 should be removed.
    2. You want it to test if you have Unpaid in the payment status. Payment status is in column B, but it's testing K.
    3. You said:
    Quote Originally Posted by alfgrey View Post
    This cell however i have conditionally formatted to display red if this cell is less than =TODAY().
    But this
    TODAY()<K2 is the Opposite of that..
    It's testing if today is less than K2


    Try
    =AND(K2<TODAY(),B2="Unpaid")

  7. #7
    Forum Contributor
    Join Date
    09-04-2013
    Location
    London
    MS-Off Ver
    Office 365
    Posts
    117

    Re: 2 rules for conditional formatting

    Thanks for your post, the formulas are wrong because i was trying variables of another suggestion earlier in the post and posted a saved copy after doing so. oops

    Erm in short it doesnt work im afraid, if i have B2 marked "unpaid" K2 is highlighted regardless of date.

    I only want K2 highlighted once it is less than TODAY(). B2 is marked Unpaid as soon as an entry is made. I just want the highlight to turn off (even if K2 is <TODAY()) When i change B2 to "Paid"

  8. #8
    Forum Guru Jonmo1's Avatar
    Join Date
    03-08-2013
    Location
    Bryan, TX
    MS-Off Ver
    Excel 2010
    Posts
    9,763

    Re: 2 rules for conditional formatting

    Works for me when today's date (Sept 4 2013) is past the date in column K
    AND Column B is UNpaid

    EFalfgrey.xlsx
    Attached Files Attached Files

  9. #9
    Forum Contributor
    Join Date
    09-04-2013
    Location
    London
    MS-Off Ver
    Office 365
    Posts
    117

    Re: 2 rules for conditional formatting

    Dont know whats different but it works a treat!! Many thanks

    No doubt ill have more questions to follow as i build this workbook, thanks again.

    Quote Originally Posted by Jonmo1 View Post
    Works for me when today's date (Sept 4 2013) is past the date in column K
    AND Column B is UNpaid

    Attachment 262835

  10. #10
    Forum Contributor
    Join Date
    09-04-2013
    Location
    London
    MS-Off Ver
    Office 365
    Posts
    117

    Re: 2 rules for conditional formatting

    one final thing.... is there a way to conditional format a cell when the value of another cell is "NO"??

  11. #11
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    44,087

    Re: 2 rules for conditional formatting

    If you are satisfied with the solution(s) provided, please mark your thread as Solved.


    New quick method:
    Select Thread Tools-> Mark thread as Solved. To undo, select Thread Tools-> Mark thread as Unsolved.

    Or you can use this way:

    How to mark a thread Solved
    Go to the first post
    Click edit
    Click Go Advanced
    Just below the word Title you will see a dropdown with the word No prefix.
    Change to Solved
    Click Save


    You may also want to consider thanking those people who helped you by clicking on the little star at the bottom left of their reply to your question.

  12. #12
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    44,087

    Re: 2 rules for conditional formatting

    when the value of another cell is "NO"??
    Which other cell?


    Regards, TMS

  13. #13
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    44,087

    Re: 2 rules for conditional formatting

    Are you saying format, say, column B if, say, column C is "NO"?

    That's why I tend to use absolute addresses, just to be sure which column is used.

    In this example, select the cells in column B (B2:Bxxx) and use the formula: =C2="NO"

    Regards, TMS

  14. #14
    Forum Contributor
    Join Date
    09-04-2013
    Location
    London
    MS-Off Ver
    Office 365
    Posts
    117

    Re: 2 rules for conditional formatting

    when i choose a customer in say cell A2 from dropdown another cell, say B2 in that row is populated either YES or NO via VLOOKUP. The adjacent cell, say C3 only needs to have data entered when B2=YES. so i wanted to conditional format Cell C3 black or with black lines through it if B3=NO to prevent users from entering data incorrectly as no data should be entered if B2 = NO

  15. #15
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    44,087

    Re: 2 rules for conditional formatting

    So, select column C, C2:Cxxx, and use the formula: =$B2="NO" and format with a black fill. It won't actually prevent data being entered but it will be a strong clue.

    Regards, TMS

  16. #16
    Forum Contributor
    Join Date
    09-04-2013
    Location
    London
    MS-Off Ver
    Office 365
    Posts
    117

    Re: 2 rules for conditional formatting

    Thanks works a treat, so I know in future why did you need $ in $B2? marking as 'solved' now too.

  17. #17
    Forum Contributor
    Join Date
    09-04-2013
    Location
    London
    MS-Off Ver
    Office 365
    Posts
    117

    Re: 2 rules for conditional formatting

    to make it absolute in column B doh shouldnt of asked

+ 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. conditional formatting rules for a cell on sheet 1 based on rules from sheet 2
    By jsard in forum For Other Platforms(Mac, Google Docs, Mobile OS etc)
    Replies: 1
    Last Post: 08-15-2013, 09:22 AM
  2. What new rules do I need for Conditional Formatting?
    By ypurcaro in forum Excel Formulas & Functions
    Replies: 10
    Last Post: 01-12-2013, 04:30 AM
  3. [SOLVED] Conditional Formatting Rules Help
    By PCAg in forum Excel Programming / VBA / Macros
    Replies: 8
    Last Post: 05-11-2012, 05:33 PM
  4. Conditional Formatting with rules
    By howcroc in forum Excel General
    Replies: 6
    Last Post: 02-04-2011, 12:01 PM
  5. Conditional Formatting with more than 3 rules
    By vickyho1008 in forum Excel General
    Replies: 5
    Last Post: 07-24-2008, 10:06 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