I have three columns of dates. The first contains the start date, the second is a date for when a 2month review is due and the third is the date that review is completed. I then set conditional formatting to turn the dates that are overdue (+5 Days-Red and +15 Days-Yellow) from current date (this part works). Now what I want to do is turn a date that is red or yellow to green if a date is entered in the third column. I can do each individual cell, but there is too much information to individually add a rule to each cell in the column. Highlighting a single cell and adding the rule (Cell Value-equal too-Green) will work but again, there are too many cells in the columns. If I highlight the entire column and try add the rule it will not let me and states that a must only apply it to a single cell.
I then tried to add a rule by formula that if this cell is empty then it will turn it green. This isn’t working. I am not sure what to do…I thought about a macro that would remove conditional formula if the dates matched up or I could even highlight it a different color if it was completed late.
It might be best to explain what I will be using it for…we have people working and they are due performance reviews. I would like to open the spreadsheet and see (visual) which ones are due by color coding their importance. Then changing that warning color to a good color (green) so I know it’s been completed.
0
votes
1 Answers
1
votes
Select your entire range (in picure below A1:C9). With selected range apply three CF rules using formulas:
- For red:
=$B1-5>=TODAY() - For yellow:
=$B1-15>=TODAY() - For green:
=$C1<>""
and using MOVE UP and MOVE DOWN buttons change the order of CF rules as shown in the picture: green should be first, yellow - second, and red - the last
