+ Reply to Thread
Results 1 to 9 of 9

How to make conditional formatting "DO NOTHING"?

  1. 06-16-2013, 03:50 PM #1
    fireboy148 is offline
    Registered User
    Join Date
    06-16-2013
    Location
    Mexico
    MS-Off Ver
    Excel 2010 mac
    Posts
    4

    How to make conditional formatting "DO NOTHING"?

    Workbook1.xlsx

    Hey Guys,

    So. I have an excel worksheet with columns that have the names of my clients (A), the day they're supposed to pay me monthly(B), and the months of the year to register their payments(D-O). Also, these clients have been colour tagged by me. I need excel to highlight from A# to B# with three conditions.

    1) If the day they are supposed to pay is within the next 7 days from today, then highlight YELLOW.

    2) If the day they are supposed to pay already passed, then highlight RED.

    3) IF (this is the part I can't get right) they already payed, then I want EXCEL to remove YELLOW OR RED (if the clients hadn't payed) and make it have the same colour as before.

    Details:

    1) I can make excel format each cell with the three conditions I want, but I want it to literally not format the cells when the client already payed.

    2) I have about 70 clients and each one of them has their own natural colour.
    Last edited by fireboy148; 06-16-2013 at 09:02 PM. Reason: Adding a workbook
    Reply With QuoteRegister To Reply
  2. 06-16-2013, 04:07 PM #2
    FDibbins is offline
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    53,048

    Re: How to make conditional formatting "DO NOTHING"?

    Hi and welcome to the forum

    If you have a cell somewhere that you enter their payment into, the you can add to the 2 rules you already have (withing 7 days/outstanding), a test to see if there is a value there, something like...

    =and(A1+7>=today(),B1<>"")
    1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
    2. If your question is resolved, mark it SOLVED using the thread tools
    3. Click on the star if you think someone helped you

    Regards
    Ford
    Reply With QuoteRegister To Reply
  3. 06-16-2013, 09:05 PM #3
    fireboy148 is offline
    Registered User
    Join Date
    06-16-2013
    Location
    Mexico
    MS-Off Ver
    Excel 2010 mac
    Posts
    4

    Re: How to make conditional formatting "DO NOTHING"?

    Mmm it didn't work. I uploaded a file which has a sample of what I want... The only problem with it is that when the client has payed up to June (which is the current month) The conditional formatting formats the row... I want it to return to its original formatting... So, instead of the rows being green... I need them to return to their original formatting
    Last edited by fireboy148; 06-16-2013 at 09:07 PM.
    Reply With QuoteRegister To Reply
  4. 06-16-2013, 09:33 PM #4
    jhren is offline
    Valued Forum Contributor
    Join Date
    05-20-2013
    Location
    Ohio, USA
    MS-Off Ver
    Excel 2013
    Posts
    941

    Re: How to make conditional formatting "DO NOTHING"?

    Ummm... where's the file?

    Never mind... I found it.
    Reply With QuoteRegister To Reply
  5. 06-16-2013, 09:41 PM #5
    jhren is offline
    Valued Forum Contributor
    Join Date
    05-20-2013
    Location
    Ohio, USA
    MS-Off Ver
    Excel 2013
    Posts
    941

    Re: How to make conditional formatting "DO NOTHING"?

    So you are saying you want the rows in your sample file that are green to be original formating instead?

    If so, bring up edit formatting dialog for the green CF, on Font and Fill tabs, click on Clear button lower right, and OK out of Format and Rule dialogs... then tick the Stop If True for this Rule in Manager.
    Reply With QuoteRegister To Reply
  6. 06-16-2013, 11:14 PM #6
    fireboy148 is offline
    Registered User
    Join Date
    06-16-2013
    Location
    Mexico
    MS-Off Ver
    Excel 2010 mac
    Posts
    4

    Re: How to make conditional formatting "DO NOTHING"?

    Quote Originally Posted by jhren View Post
    So you are saying you want the rows in your sample file that are green to be original formating instead?

    If so, bring up edit formatting dialog for the green CF, on Font and Fill tabs, click on Clear button lower right, and OK out of Format and Rule dialogs... then tick the Stop If True for this Rule in Manager.


    Yeah. I tried your suggestion, It makes total sense and I had actually already tried it, but for some reason it just doesn't work. Could it be my Mac version of Excel? :/
    Reply With QuoteRegister To Reply
  7. 06-16-2013, 11:24 PM #7
    jhren is offline
    Valued Forum Contributor
    Join Date
    05-20-2013
    Location
    Ohio, USA
    MS-Off Ver
    Excel 2013
    Posts
    941

    Re: How to make conditional formatting "DO NOTHING"?

    Quote Originally Posted by fireboy148 View Post
    Yeah. I tried your suggestion, It makes total sense and I had actually already tried it, but for some reason it just doesn't work. Could it be my Mac version of Excel? :/
    Don't know. Attached file from Windoze Excel 2010. See what it does in your Excel...
    Attached Files Attached Files
    Reply With QuoteRegister To Reply
  8. 06-17-2013, 12:04 AM #8
    fireboy148 is offline
    Registered User
    Join Date
    06-16-2013
    Location
    Mexico
    MS-Off Ver
    Excel 2010 mac
    Posts
    4

    Re: How to make conditional formatting "DO NOTHING"?

    Quote Originally Posted by jhren View Post
    Don't know. Attached file from Windoze Excel 2010. See what it does in your Excel...
    Well... That's weird. It's perfect in your file, but when I try it just doesn't work... So strange... It has the same formulas and conditional formatting. Maybe I need to Format them in a Windows computer and then transfer the files to my Mac.
    Reply With QuoteRegister To Reply
  9. 06-17-2013, 12:30 AM #9
    jhren is offline
    Valued Forum Contributor
    Join Date
    05-20-2013
    Location
    Ohio, USA
    MS-Off Ver
    Excel 2013
    Posts
    941

    Re: How to make conditional formatting "DO NOTHING"?

    Are you setting the Rule initially to change fill formatting to green (as in the example) then using the Clear button? ...and it doesn't clear the fill? If so, perhaps the Clear button is malfunctioning.

    Try deleting the existing Rule (copy formula first) and recreating the Rule (make sure the proper cell or row is selected for cell referenced in the formula) and only set desired formatting. Also make sure the Rule is above the other row formatting rules... and set for Stop If True.

    Another method is to make this Rule's formula part of the ones that change the rows to yellow or red. I didn't look at those formulas, but the basic method is to wrap the existing conditional argument inside an AND function, then include your "do not format" argument wrapped inside a NOT function, or otherwise change to get FALSE when formatting is not wanted.
    Reply With QuoteRegister To Reply
+ Reply to Thread
« Previous Thread | Next Thread »

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

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

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1