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 tha...
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?
- 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.