Forum Discussion
Solved
Formula Help
I am using Excel for Mac version 16.96 (Office Home & Business 2021).
I am trying to create 2 different formulas within a cell. The first one calculates based on another cell. So, if E2 has "Y" in it, I want F2 to turn red. I used conditional formatting, classic formatting rule, use a formula, and then put =E12="Y" and formatted it with light red fill and dark red background. It does exactly what I want.
I followed the same steps and put the formula =F2="Y" and have it turn green. It works. But they can't both work. If I fill E2 first with a Y, then it turns red. But when I then type Y in F2, it does not later turn green. It stays red.
I'm trying to highlight a box red to tell me that the item has arrived (E2 Y is for yes, it has arrived) but I still need to do something with it. The red makes it easier to notice. But once I put a Y in F2 (because I did something with it), I need the box to turn green so I know I'm done with that item.
Is there a way to accomplish this? Since I know you can make a cell turn red or green based on the information in that particular cell, I know Excel is set up to handle this. I just don't know how to make it work with looking at the data in a different cell for one color and looking at the data in that cell for the other color.
Thank you for any assistance. For now, I've been keeping the formulas and then deleting the red formula once I need it to turn green, which is not sustainable.
In the conditional formatting you just need the ORDER of the rules to be correct. In the below image you can see the cell is green even though both conditions are true. IF I reverse the order then it will stay red. To move the order there are up down arrows to the right of the "Duplicate Rule" button. Note you can also use the 'Stop if True' checkbox to the right of the rule. So in this example, in addition to making the background red it also makes the text YELLOW and BOLD, which is still happening but if the 'Stop if True' checkbox is clicked then that additional formatting will not happen.
2 Replies
In the conditional formatting you just need the ORDER of the rules to be correct. In the below image you can see the cell is green even though both conditions are true. IF I reverse the order then it will stay red. To move the order there are up down arrows to the right of the "Duplicate Rule" button. Note you can also use the 'Stop if True' checkbox to the right of the rule. So in this example, in addition to making the background red it also makes the text YELLOW and BOLD, which is still happening but if the 'Stop if True' checkbox is clicked then that additional formatting will not happen.
Thank you! I wish I had thought to try that. It was such a simple solution. I appreciate it.
