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