Terri Cannon
asked on
Conditional Formatting Formulas
I need conditional formatting formulas to apply to whole columns, if possible. I want to have cells that contain an X to change color based on the expiration dates in another cell. Green if within 120-91 days, yellow if within 90-61 days, red if within 60-31 days, black if 30 days and below, all . All cells that have a date more than 120 days or are blank should remain unformatted.
Example: Column A contain students names, column B contain due dates and columns C-G contain assignments. If a student has not completed the assignment an X would be placed in the cell under that assignment. Once student is within 120 days to the due date color should change to green.
NAME DUE DATE ENGLISH MATH READING HISTORY COMMUNITY SERVICE
JOHN DOE 13-AUG-17 X X X
Example: Column A contain students names, column B contain due dates and columns C-G contain assignments. If a student has not completed the assignment an X would be placed in the cell under that assignment. Once student is within 120 days to the due date color should change to green.
NAME DUE DATE ENGLISH MATH READING HISTORY COMMUNITY SERVICE
JOHN DOE 13-AUG-17 X X X
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thank you so much Shums and Ryan Chong, this works great.
ASKER
Thank you for your help. I could not get my formulas to work together. Your solutions were perfect. Thanks again.
You're Welcome Terri! Glad we're able to help you.
hmm did you try mysuggestion? just wondering.
=AND(DATEDIF($C2,NOW(),"D"
just change the values to the ones you want for each color.