# Microsoft Office Application Help - Excel Help forum > Excel Formulas & Functions >  >  Formula to change font color

## logan_fernandes

I am trying to figure out what the formula would be to change the font color if the entered number is less than the quota.

I'm making a spreadsheet for work that each of the reps will punch in their daily numbers, and, for example, if there quota was 70 and they are <70 than the font will turn red, and if greater or equal to 70 it will turn green.

Thanks in advance!

----------


## DonkeyOte

You can't use an in-cell formula to do this however you could use either

a) Custom Format

or

b) Conditional Format

to do this... 

the latter certainly offers more flexibility but is "super-volatile" so is best used in moderation... 

If you intend to only adjust the font colour (not the interior background) and have fixed boundaries then you can use a basic Custom Format... using your example:

*[red][<70]#.00;[COLOR 10]#.00*

If neither of the above conditions hold true then you should use Conditional Formatting, see: http://www.contextures.com/xlCondFormat01.html

----------


## logan_fernandes

The conditional formatting is JUST what I was looking for. Perfect!

Now, my next question is:

I want to make it so that if one column has green text (Based on the conditional formatting) that another column, or more specifically set of cells will be green as well. Same thing with the red. Is that possible?

----------


## DonkeyOte

see: http://www.contextures.com/xlCondFormat02.html

----------


## logan_fernandes

Hmm, that seemed like the perfect solution, again, for what I wanted. But for some reason I can't get it to work.

Essentially, what I want my spreadsheet to do (now) is if C2 is less than 70 then B2 font will be red, and if C2 is greater than or equal to 70 than B2s font will be green. This will continue down the whole row of B and C

I've mimicked their formula but it's just not working for some reason.
This is what I'm trying under conditional formatting for the cell;

First I select Cell B2, and under the format

Formula is =$C2<=70 (select red font)
and the other is
Formula is =$C2>=70 (select green font)

What am I doing wrong?

----------


## Palmetto

> What am I doing wrong?



You're confusing Excel. If the actual value is exactly 70, Excel can't determine which CF applies because your formula states if the value is <= 70 then (red font), but if >= 70 then (Green font). Since a value of 70 meets both conditions, Excel can't apply the CF.

Use:
=$C2<70
=$C2>=70

----------


## logan_fernandes

> You're confusing Excel. If the actual value is exactly 70, Excel can't determine which CF applies because your formula states if the value is <= 70 then (red font), but if >= 70 then (Green font). Since a value of 70 meets both conditions, Excel can't apply the CF.
> 
> Use:
> =$C2<70
> =$C2>=70



Yeah, I'm an idiot, I just typed it wrong, I actually had it just like you wrote, and it still isnt working.

----------


## logan_fernandes

And also, if need be, I can attach my spreadsheet so you can see exactly what I'm trying to do.

----------


## Palmetto

See attached example.

----------


## logan_fernandes

Thanks, but I have that part down, I understand the conditional formatting to change the color of the selected cell, based on the supplied criteria, but how do I change the color of a cell, based on ANOTHER cells criteria. 

The goal is to make B2 change color based on what is in C2.

For example, if C2 is greater than or equal to 70, than B2's font color would change to green, and if C2 is less than 70, B2 will go green.

Let me know if you need anything else?

----------


## NBVC

> Yeah, I'm an idiot, I just typed it wrong, I actually had it just like you wrote, and it still isnt working.



When you go back and look at the CF do you see quotes around the formulas?  If yes, remove them..

If no, attach a workbook showing problem.

----------


## Palmetto

See amended example in the workbook in my previous post.

Excel Conditional Formatting Based on another cell

----------


## logan_fernandes

Here is an attachment of my workbook

----------


## logan_fernandes

Nevermind!! This worked!! Palmettos amended example was exactly what I needed! Thanks a lot guys!

You rock!

Alright, thanks so much for your help everyone, I greatly appreciate it. We can close this thread now  :Smilie:

----------


## Palmetto

To mark the thread as solved, go to YOUR first post at the top and click on Thread Tools, then Go Advanced, choose the [Solved] prefix.

Don't forget to leave feedback and add to the reputation of those who contributed a helpful response. See my signature for how to go about it.

----------


## Mriley13

Hi,

I'm trying to do the same thing. could this work with decimals. I have my spreadsheet and I need the color to change to red if it's less than $7.79 and stay black if is more than $7.79. I tried the formula posted before but for some reason is not working. Please help. This would make my life so much easier.

----------

