Forum Discussion
Conditional Formatting
Using excel 16.69- It appears using the new excel is not as easy as past editions. That being said. I want to use conditional formating to do the following condition on a budget. I want to change cell A3 to yellow when remaining balance value reaches 85% of allotted value in cell D3. The cell is green when under budget and goes to red when over budget. I've tried multiple ways but to no avail.
Thanks for any help
Lonnie
I want to change cell A3 to yellow when remaining balance value reaches 85% of allotted value in cell D3. The cell is green when under budget and goes to red when over budget.
But 85% is, itself, "under budget." So unless you're talking of different cells with the green-yellow-red sequence, you have conflicting criteria here.
Is it possible for you to depict things a bit more fully? How have you spelled out the conditional formatting rules that ARE working for the Green and Red conditions?
It may be that the solution is to have more precisely defined conditions
- green when less than or equal to 84.999%
- yellow when between 85 and 99.999%
- red when >100%
This is the pop up I see, I don't see how I can do the percentages for yellow. I can do green using a calculated number. For example 425 for 85% to turn green and 501 to turn red.
any help is appreciated.
- green when less than or equal to 84.999%
- yellow when between 85 and 99.999%
- red when >100%
Still not knowing exactly how you've arrayed your full sheet, here's a suggestion as to how to accomplish the conditional formatting. I use the Classic ... Formula approach,
and I've attached the sample worksheet to this message.
Here is a link for further study on conditional formatting, which can be tricky. I often have to try several times to get it to work as desired. So don't be too hard on yourself.
I have greater than turn cell red. I have less than turn cell green. I see what you are saying anything less than meets green criteria.
I Agee with your recommendation to fits exactly what I am looking for.
Thanks
Not sure how it has changed or is harder but you can create 2-3 rules each to color cell A3 based on formula of D3 (you can use 2 rules by setting the fill color to 1 color and use 2 rules to change the color).
Another option is to use a single 'icon sets' to create green, yellow, red dots (or arrows) based on ranges as follows:
