+ Reply to Thread
Results 1 to 4 of 4

Please help: conditional formatting based on two cell values!

Hybrid View

DanniMc Please help: conditional... 05-12-2016, 11:14 AM
etaf Re: Please help: conditional... 05-12-2016, 11:20 AM
DanniMc Re: Please help: conditional... 05-12-2016, 11:44 AM
etaf Re: Please help: conditional... 05-12-2016, 01:57 PM
  1. #1
    Registered User
    Join Date
    04-21-2016
    Location
    Bath, UK
    MS-Off Ver
    2016
    Posts
    2

    Question Please help: conditional formatting based on two cell values!

    Hi

    I have done this before but seem unable to get it right this time:

    I have a invoice spreadsheet and want to conditionally format cells in the 'due date' column to show when an invoice hasn't been paid on time.

    On the spreadsheet:

    Column A = DUE DATE
    Column B = numerical amount if paid, or 'np' if not paid.

    How do I conditional format column A to highlight cells if today's date is greater than the date in cell A and there is 'np' in cell B??

    Any help would be great. This is really niggling me and I know I will feel silly when I hear the answer but my mind is BLANK!!

    Thanks

  2. #2
    Forum Expert etaf's Avatar
    Join Date
    10-22-2004
    Location
    Wittering, West Sussex, UK
    MS-Off Ver
    365 (Mac OSX) (16.92 (24120731))
    Posts
    9,101

    Re: Please help: conditional formatting based on two cell values!

    use a formula
    Assuming the range starts at row 2

    use a range of A2 to A???

    =AND(B2="NP", today() >A2 )
    Wayne
    if my assistance has helped, and only if you wish to , there is an "* Add Reputation" on the left hand side - you can add to my reputation here

    If you have a solution to your thread - Please mark your thread solved do the following: >
    Select Thread Tools-> Mark thread as Solved. To undo, select Thread Tools-> Mark thread as Unsolved.

  3. #3
    Registered User
    Join Date
    04-21-2016
    Location
    Bath, UK
    MS-Off Ver
    2016
    Posts
    2

    Re: Please help: conditional formatting based on two cell values!

    This is highlighting some cells, but not the right ones!! I'm sop confused - I an't even work out WHY it is highlighting the ones it is!!

  4. #4
    Forum Expert etaf's Avatar
    Join Date
    10-22-2004
    Location
    Wittering, West Sussex, UK
    MS-Off Ver
    365 (Mac OSX) (16.92 (24120731))
    Posts
    9,101

    Re: Please help: conditional formatting based on two cell values!

    the range has to match the formula

    so if you select A:A

    then the formula has to be row 1
    =AND(B1="NP", today() >A1 )

    if you specify a range say
    A50:A200
    then the formula has to start at 50
    =AND(B50="NP", today() >A50 )

    otherwise the formatting seems off

    perhaps load a sample sheet

    i have made a simple sheet , added the conditional formatting
    and also added the formula in a cell
    Attached Files Attached Files
    Last edited by etaf; 05-12-2016 at 05:22 PM.

+ 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. Help with conditional formatting based on cell values
    By ExcelIsTheWayForward in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 01-26-2015, 07:41 AM
  2. Conditional Formatting based on 2 cell values
    By ksmith767 in forum Excel - New Users/Basics
    Replies: 2
    Last Post: 02-28-2014, 01:13 PM
  3. [SOLVED] Conditional formatting based on two cell values
    By Thinker8 in forum Excel General
    Replies: 7
    Last Post: 09-22-2013, 12:50 AM
  4. [SOLVED] Conditional Formatting Based on Values in another Cell
    By jdegeorge in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 09-12-2013, 02:35 PM
  5. Conditional Formatting based on 3 different cell values
    By Stephen.Birch in forum Excel - New Users/Basics
    Replies: 2
    Last Post: 03-21-2013, 10:16 AM
  6. Conditional Formatting based on cell values
    By nickbrandon in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 03-05-2013, 08:26 AM
  7. [SOLVED] conditional formatting based on values of another cell
    By annysjunkmail@tiscali.co.uk in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 09-01-2005, 04:05 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