Forum Discussion
Conditional Formula?
I'm trying to make a conditional formula where if the logical test (=if(logical test) results in a number in another cell being greater the value of the formula cell returns a value with a color. Is this at all possible?
i.e. if the value is higher the color is green if not the color is red
3 Replies
A formula can return a value, but it cannot directly change the cell color.
Use Conditional Formatting for the color.
Select the cell, then go to:
Home > Conditional Formatting > New Rule > Use a formula
For green:
=C1>B1
For red:
=C1<=B1
So the formula handles the value, and Conditional Formatting handles the color.
A regular formula in a cell can only show a value, not change colors. To get a color based on a number in another cell, you use Conditional Formatting.
Here’s a quick way to do it:
Let’s say your number is in A1, and you want B1 to be green if A1 is greater than 10, and red if not.
- Select cell B1.
- Go to Home → Conditional Formatting → New Rule.
- Choose “Use a formula to determine which cells to format”.
- In the formula box, type the test for green:
=A1>10
(no IF needed—just the condition) - Click Format, pick a green fill, then OK.
- Click New Rule again, and this time enter the red condition:
=A1<=10
Set the fill to red, then OK.
Now B1 will automatically change color when the number in A1 changes.
You can adjust the cell references and the number to suit your sheet.
Hope that helps!
Have a look at Conditional Formatting (button on the Home tab of the ribbon), Highlight cells rules, Greater Than.
