+ Reply to Thread
Results 1 to 6 of 6

Assigned conditional formatting scrambling on sort.

  1. #1
    Registered User
    Join Date
    08-07-2014
    Location
    United States
    MS-Off Ver
    2010
    Posts
    3

    Question Assigned conditional formatting scrambling on sort.

    Hello. I am making a system that warns me when the Monthly, Bimonthly, and Quarterly appointments are due based on calculating the "Last Serviced" date. The problem is that when I sort the data (usually by company name or by service type) it scrambles up the color coding and places the assigned conditional formatting rules in the wrong places. How can I keep the conditional formatting attached to their respective companies and row (and still be able to sort without scrambling).

    I attached the file I need help with. I just removed the sensitive data.
    Help me out and win a PC game for Steam.


    Thank you.
    Attached Files Attached Files

  2. #2
    Valued Forum Contributor
    Join Date
    01-10-2012
    Location
    Texas
    MS-Off Ver
    Excel 2010
    Posts
    650

    Re: Assigned conditional formatting scrambling on sort.

    I've made your conditional formatting rules dynamic using formulas like this one:
    Formula: copy to clipboard
    Please Login or Register  to view this content.


    This should fix the problem. Let me know if you have any questions or issues with the new formula.

    What do you want for the one that says "SEE NOTES" ...?
    Attached Files Attached Files

  3. #3
    Registered User
    Join Date
    08-07-2014
    Location
    United States
    MS-Off Ver
    2010
    Posts
    3

    Re: Assigned conditional formatting scrambling on sort.

    On E13 of your fixed document change the date from 6/26/2014 to 5/26/2014. It should change to red but it turns to green. Thank you for helping.

  4. #4
    Valued Forum Contributor
    Join Date
    01-10-2012
    Location
    Texas
    MS-Off Ver
    Excel 2010
    Posts
    650

    Re: Assigned conditional formatting scrambling on sort.

    sorry about that... had a couple of my formulas flipped (< instead of >)

    this one should be fixed.

    note - blank cells currently turn red. that can be easily changed if you want with a new rule for which the formula is =e2="", applied to $A$2:$A$22 and click the "stop if true" checkmark on the right.
    Attached Files Attached Files

  5. #5
    Registered User
    Join Date
    08-07-2014
    Location
    United States
    MS-Off Ver
    2010
    Posts
    3

    Re: Assigned conditional formatting scrambling on sort.

    Thank you for the fix. I had to modify it a little more.


    GREEN
    =OR(AND(C2="Monthly",E2+20>=TODAY()),AND(C2="Bimonthly",E2+50>=TODAY()),AND(C2="Quarterly",E2+80>=TODAY()))
    ORANGE
    =OR(AND(C2="Monthly",E2+21<TODAY()),AND(C2="Bimonthly",E2+51<TODAY()),AND(C2="Quarterly",E2+81<TODAY()))
    RED
    =OR(AND(C2="Monthly",E2+30<TODAY()),AND(C2="Bimonthly",E2+60<TODAY()),AND(C2="Quarterly",E2+90<TODAY()))



    But, its basically the same format. Thank you. Would you like a Steam game gift code sent to your email? I have Dead Island Epidemic and a couple others I can offer.

  6. #6
    Valued Forum Contributor
    Join Date
    01-10-2012
    Location
    Texas
    MS-Off Ver
    Excel 2010
    Posts
    650

    Re: Assigned conditional formatting scrambling on sort.

    glad it's working for you... I could be wrong, but it looks an awful lot like 21, 51 or 81 days after the last monthly, bimonthly or quarterly appointment the cell will not be shaded at all (your ranges go from 0 - 20 and then start at 22 since you have < in there instead of <=)

    as for the game - only if you won't use it... i'm not sure if I would or not. steam looks pretty cool but I don't really spend any time on a personal computer these days...

+ 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 on shape assigned macro
    By arvindm in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 08-07-2013, 11:10 AM
  2. Replies: 0
    Last Post: 06-13-2012, 11:02 PM
  3. Replies: 5
    Last Post: 05-25-2012, 02:00 PM
  4. conditional format based on sum of value assigned to partial text field
    By rshiley in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 12-19-2010, 06:44 PM
  5. Sort conditional formatting
    By meridklt in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 10-18-2007, 04:38 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