Forum Discussion
Solved
Conditional Formatting using formula
I have gotten this to work but it's very temperamental and I often have to start over. Figured there's a better way.
Column A is a list of Project codes
Column B is a monthly budget assumption that remains constant for the year.
Column C is a 3-month minimum threshhold and Column D is a 6-month maximum threshhold.
Columns I - T are months where the monthly "Actuals" are entered.
For each Project code at far left, that month's "actual" is entered in the appropriate month.
If the amount is LESS than the 3-month reserve requirement, it should appear PINK
If the amount is within the 3-6 month reserve requirement, it should appear YELLOW
If the amount is GREATER than the 6-month requirement, the cell should appear GREEN
As you can see, I've gotten it to work but it's VERY cumbersome with lots of rules and fails when I add or insert a new project code row.
Appreciate any help!
CCORUM It seems like you have the formulas created correctly, but you need to work on the rules so that ideally you will only have 3 rules for the entire range. You can adjust the formulas so that if the cell doesn't have a value, no formatting will be applied.
If you have just 3 rules and the entire range is mentioned in the "Applies To" field, then it will be more resilient to inserting new rows.
It would help if you share a screen shot of the Manage Rules dialog. Choose "This Worksheet" in the drop-down at the top so you can see all the rules.
CCORUM - were you able to reduce it to just 3 rules?
