Forum Discussion
Simplifying Multiple Conditional Formatting Equations
Hello,
I am hoping to get some help simplifying the mess of conditional formatting rules that are currently set up in an Excel sheet we use. We have a list of accounts and multiple tasks that we need to complete throughout the year for each. Our current formatting rules are a mess. They work, but they are copied from one line to the next and only seem to cover one or two cells at a time. Below is what it looks like now...
Is there a way to consolidate these or make more of a "batch" rule to cover whole columns? Here is what we are looking to accomplish with the conditional formatting:
- If a task is complete, we type "Done" into cell O2. We want that to trigger a green fill for cells O2 and P2 (which contains the date we last contacted the customer).
- If the date in P2 is longer than 10 day ago, we want P2 to be filled yellow.
- If the date longer than 20 days ago, we want P2 to be filled red.
- If cell N2 contains "CANCELLED", we want the row from cells A to Z to be filled dark grey with a strikethrough text.
Thank you and let me know if any additional information would be helpful!
2 Replies
Remove all current rules.
Let's say you have or expect data in rows 2 to 150.
1) Select P2:P150.
P2 should be the active cell in the selection.
On the Home tab of the ribbon, click Conditional Formatting > New Rule...
Select 'Use a formula to determine which cells to format'.
Enter the formula=AND($P2<>"", $P2<TODAY()-10)
Click Format...
Activate the Fill tab.
Select yellow as fill color.
Click OK, then click OK again.2) Repeat step 1) but with
=AND($P2<>"", $P2<TODAY()-20)
and red as fill color.
3) Select O2:P150.
O2 should be the active cell in the selection.
On the Home tab of the ribbon, click Conditional Formatting > New Rule...
Select 'Use a formula to determine which cells to format'.
Enter the formula=$O2="Done"
Click Format...
Activate the Fill tab.
Select light green as fill color.
Click OK, then click OK again.4) Select A2:Z150.
A2 should be the active cell in the selection.
On the Home tab of the ribbon, click Conditional Formatting > New Rule...
Select 'Use a formula to determine which cells to format'.
Enter the formula=$O2="Cancelled"
Click Format...
Activate the Fill tab.
Select dark grey as fill color.
Activate the Font tab.
Make sure the Strikethrough check box is selected.
Click OK, then click OK again.This worked perfect! Thank you so much for helping us clean that up!
