Forum Discussion
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.
- Yes. I think you should only need 3 CF rules to accomplish what you want. You will need a slight adjustment to your formulas. Taking the first one in your screen shot as an example, if you remove the $ from in front of N6, then it will work correctly for the actuals in columns beyond N that are part of your Applies To range:
=AND(N6>$E6,N6<$F6)
To test it, you can put this formula into a cell to the right of your range, like S6, then drag it down and then drag to the right, you should see that it adjusts correctly and gives you the expected TRUE/FALSE result depending on whether the actuals are within the threshold. The reference to E and F will stay, but everything else will adjust accordingly. In conditional formatting, you can imagine that you have filled the formula across and down the Applies To range.
If your Applies To range is =$I$6:$T100, then the rule formula would be =AND(I6>$E6, I6<$F6).
Just make sure that your formula refers to the cell with the actual in the first row and column of your Applies To range.
I think you can delete all but one of each rule (yellow, pink, green), and set the Applies To so that it has just one reference, and that reference should be for the entire range that you want to cover.
