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.

I've made your conditional formatting rules dynamic using formulas like this one:

Formula:

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" ...?

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.

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.

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.

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...