We help IT Professionals succeed at work.

Conditional Formatting Formulas

207 Views
Last Modified: 2017-04-05
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
Comment
Watch Question

CERTIFIED EXPERT

Commented:
create as many rules as colors you need
=AND(DATEDIF($C2,NOW(),"D")>180,DATEDIF($C2,NOW(),"D")<200)

just change the values to the ones you want for each color.

XymCmaO-1-.png
ShumsManaging Director/Excel VBA Developer
CERTIFIED EXPERT
Distinguished Expert 2018
Commented:
Unlock this solution and get a sample of our free trial.
(No credit card required)
UNLOCK SOLUTION
Software Tead Lead / Business Analyst / System Analyst / Data Engineer / Applications Consultant
CERTIFIED EXPERT
Commented:
Unlock this solution and get a sample of our free trial.
(No credit card required)
UNLOCK SOLUTION

Author

Commented:
Thank you so much Shums and Ryan Chong, this works great.

Author

Commented:
Thank you for your help. I could not get my formulas to work together. Your solutions were perfect.  Thanks again.
ShumsManaging Director/Excel VBA Developer
CERTIFIED EXPERT
Distinguished Expert 2018

Commented:
You're Welcome Terri! Glad we're able to help you.
CERTIFIED EXPERT

Commented:
hmm did you try mysuggestion? just wondering.
Unlock the solution to this question.
Thanks for using Experts Exchange.

Please provide your email to receive a sample view!

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.