Forum Discussion
Conditional Formatting
Good morning,
Well I tried plugging in the formulas that you suggested and it worked, sort of. I skipped the first condition because it wasn't necessary. The third condition formula worked like I needed it to. But the second condition is coloring the cells yellow before it reached the 7 day mark. I have attached a screen shot to show you what I mean. I double checked to make sure I wasn't missing anything in the formula and its just as you wrote it. My co-worker says it's possessed. Any ideas?
Thanks,
Carol
The mistake was mine Carol! Try using this for the second formula instead:
=AND(7>=(C2-TODAY()),(C2-TODAY())>0)
Perfect! Thank you so much!
One more question, what if I wanted to make it change to green when I document that the task was completed. Would I have to add a whole new column to capture that?
Clever use case! Here's how you would go about doing that:
1. Select the same cells that you want colored (column C minus the header)
2. Home > Conditional Formatting > New Rule
3. By formula (as before) > and in the box you would put something like
=D2="Complete"
Fill color: Green
Since you seem to be understanding this and it's working, I'll give you two tips here.
1. If you select a range of cells and then put a formula in the box for conditional formatting, it will increase all row numbers by one as it applies it to each cell. That's how you are able to apply these rules on a large scale by only referencing one cell in the formula. Another way to say is that for cell C2, it will use D2 in the formula, but for cell C3 it will use cell D3 in the formula.
2. You can manage the priority order of the rules by clicking Home > Conditional Formatting > Manage rules. If you don't see your rules in the list, change the drop down at the top to "This worksheet." This is the priority list of the rules. You can change the order by clicking the rule once and using the arrows to move it up or down. For instance, if you want the cell to be green if it says complete, no matter what the date rules say, then that should be on top.
Hope that helps!