Color changing formula's

[MrHitman07]

Can someone either give me the formula or redirect me to a thread about changing the text color of numbers based on value.
I.e. I need for negitive numbers to be RED Positive numbers to be BLACK and 0 to be YELLOW like below.
1.0
0.0
-1.0

This formula will be use for a huge data base and only one column needs to be affected by it. Currently I had click the option to change color but it becomes a hassle and there is room for error. I wish to put the data in the column and not worry about the color.

[sweep]

Hi,

Select the column, then go to

Format > Conditional Formatting

and set your formats as required

[NBVC]

Select the cells to affect...

Go to Format|Conditional Format

Select Cell Value Is from 1st drop down and select Is Equal To from next drop down. Then enter =0 in formula field. Click Format and choose yellow from Font tab.

Click ok.

Clikc Add and select Cell Value Is >>> Is Less Than and enter =0. Click Format and choose Red.

Click oK

Click Ok again to finish.

[dominicb]

Good afternoon MrHitman07

...and welcome to the forum!!

Highlight the whole of the column that you want to affect.
Press Ctrl + 1, and under the Number tab, select the category Custom.
Type this into the box and click OK.

[Red][<0]-#,##0;[Black][>=1]#,##0;[Yellow]#,##0

HTH

DominicB

[Richard Schollar]

Can someone either give me the formula or redirect me to a thread about changing the text color of numbers based on value.
I.e. I need for negitive numbers to be RED Positive numbers to be BLACK and 0 to be YELLOW like below.
1.0
0.0
-1.0

This formula will be use for a huge data base and only one column needs to be affected by it. Currently I had click the option to change color but it becomes a hassle and there is room for error. I wish to put the data in the column and not worry about the color.

As Sweep says, you *could* use CF for this, but utilising CF imposes certain overheads and restrictions on your workbook. Instead, you can simply use a custom format:

Select the cells/whole column and go Format>Cells>Number tab and click the Custom category. In the Type box type:

0.00;[red]-0.00;[yellow]0.00

and click OK.

Richard

[MrHitman07]

so basically, if cell value is less than 0.0 how do I make it red? I'm a noob

[Richard Schollar]

Hi

Have you followed the steps I gave in my post?

Richard

[MrHitman07]

ya dude I did, I was responding to sweep with that last reply. urs took a while to refresh for some reason, but Thanks a bundle it works I exactly the way I want it to now rich

[John K]

I can do as it states above, changing A1 to green when C1>0.
How do it tell the entire column to do the same with the A column and C column being attached in the same row?
What I want to do is set up the A column (same row ) to change color based upon entries in the C column.
I tried autofill and it impacts all of A column when C1 is >0
Can I get some help with this?
Thanks, John

[Bryan Hessey]

I can do as it states above, changing A1 to green when C1>0.
How do it tell the entire column to do the same with the A column and C column being attached in the same row?
What I want to do is set up the A column (same row ) to change color based upon entries in the C column.
I tried autofill and it impacts all of A column when C1 is >0
Can I get some help with this?
Thanks, John

Hi,

Select the A column, and cell A1 highlights a different colour, enter the Conditional format as it would apply to that (A1) cell, then set the required Font colour.

hth
---