# Microsoft Office Application Help - Excel Help forum > Excel Formulas & Functions >  >  How to make the cell or font color red if the number is negative?

## Bob Phillips

Use conditional formatting with a condition of less than 0.

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Bob T" <Bob T@discussions.microsoft.com> wrote in message
news:995EE172-AAE8-46EE-ADB4-5DF46F9D3806@microsoft.com...
> I'd like to make a cell change color or the cell's font change color as
the
> number in the cell changes from negative to positive or vice versa?

----------


## Max

Try Conditional Formatting

Suppose you want this to happen in say, col E
Select col E
Click Format > Conditional Formatting
Make the settings as:

For Condition 1:
Formula Is| =AND(E1<>"",ISNUMBER(E1),E1>=0)
Click Format button > Patterns tab > Light green? > OK

Click Add

For Condition 2:
Formula Is| =AND(E1<>"",ISNUMBER(E1),E1<0)
Click Format button > Patterns tab > Pink? > OK

Click OK at the main dialog

Now test it out by inputting some negative, positive values, zeroes and some
text (e.g.: "ABC") in col E ..

The cells' fill color should appear accordingly for the numbers, any cell
with text entered or empty cells should not trigger the C.F. (i.e. no
formatting should occur)
--
Rgds
Max
xl 97
---
GMT+8,  1Â° 22' N  103Â° 45' E
xdemechanik <at>yahoo<dot>com
----
Bob T <Bob T@discussions.microsoft.com> wrote in message
news:995EE172-AAE8-46EE-ADB4-5DF46F9D3806@microsoft.com...
> I'd like to make a cell change color or the cell's font change color as
the
> number in the cell changes from negative to positive or vice versa?

----------


## Bob T

I'd like to make a cell change color or the cell's font change color as the
number in the cell changes from negative to positive or vice versa?

----------


## Bob T

Thanks Bob! That's exactly what I was looking for

"Bob Phillips" wrote:

> Use conditional formatting with a condition of less than 0.
>
> --
>
> HTH
>
> RP
> (remove nothere from the email address if mailing direct)
>
>
> "Bob T" <Bob T@discussions.microsoft.com> wrote in message
> news:995EE172-AAE8-46EE-ADB4-5DF46F9D3806@microsoft.com...
> > I'd like to make a cell change color or the cell's font change color as
> the
> > number in the cell changes from negative to positive or vice versa?
>
>
>

----------


## Bob T

Thanks Max! Your answer was very helpful, exactly what I wanted.

"Max" wrote:

> Try Conditional Formatting
>
> Suppose you want this to happen in say, col E
> Select col E
> Click Format > Conditional Formatting
> Make the settings as:
>
> For Condition 1:
> Formula Is| =AND(E1<>"",ISNUMBER(E1),E1>=0)
> Click Format button > Patterns tab > Light green? > OK
>
> Click Add
>
> For Condition 2:
> Formula Is| =AND(E1<>"",ISNUMBER(E1),E1<0)
> Click Format button > Patterns tab > Pink? > OK
>
> Click OK at the main dialog
>
> Now test it out by inputting some negative, positive values, zeroes and some
> text (e.g.: "ABC") in col E ..
>
> The cells' fill color should appear accordingly for the numbers, any cell
> with text entered or empty cells should not trigger the C.F. (i.e. no
> formatting should occur)
> --
> Rgds
> Max
> xl 97
> ---
> GMT+8,  1ÃÂ° 22' N  103ÃÂ° 45' E
> xdemechanik <at>yahoo<dot>com
> ----
> Bob T <Bob T@discussions.microsoft.com> wrote in message
> news:995EE172-AAE8-46EE-ADB4-5DF46F9D3806@microsoft.com...
> > I'd like to make a cell change color or the cell's font change color as
> the
> > number in the cell changes from negative to positive or vice versa?
>
>
>

----------


## Max

You're welcome, Bob !
Thanks for the feedback
--
Rgds
Max
xl 97
---
GMT+8,  1Â° 22' N  103Â° 45' E
xdemechanik <at>yahoo<dot>com
----
"Bob T" <Bob T@discussions.microsoft.com> wrote
> Thanks Max! Your answer was very helpful, exactly what I wanted.

----------


## RODY

HI BOB TRY USING CONDITIONAL FORMATING GO TO TOOLS MENU / CONDITIONAL
FORMATING /  CELL VALUE IS / NOT EQUAL TO / TYPE 0 / CLICK FORMAT / CLICK THE
BUTTON WOULD YOU LIKE  TO DO

"Bob T" wrote:

> I'd like to make a cell change color or the cell's font change color as the
> number in the cell changes from negative to positive or vice versa?

----------


## RODY

HI BOB TRY USING CONDITIONAL FORMATING GO TO TOOLS MENU / CONDITIONAL
FORMATING /  CELL VALUE IS / NOT EQUAL TO / TYPE 0 / CLICK FORMAT / CLICK THE
BUTTON WOULD YOU LIKE  TO DO

"Bob T" wrote:

> I'd like to make a cell change color or the cell's font change color as the
> number in the cell changes from negative to positive or vice versa?

----------


## RODY

HI BOB TRY USING CONDITIONAL FORMATING GO TO TOOLS MENU / CONDITIONAL
FORMATING /  CELL VALUE IS / NOT EQUAL TO / TYPE 0 / CLICK FORMAT / CLICK THE
BUTTON WOULD YOU LIKE  TO DO

"Bob T" wrote:

> I'd like to make a cell change color or the cell's font change color as the
> number in the cell changes from negative to positive or vice versa?

----------


## RODY

HI BOB TRY USING CONDITIONAL FORMATING GO TO TOOLS MENU / CONDITIONAL
FORMATING /  CELL VALUE IS / NOT EQUAL TO / TYPE 0 / CLICK FORMAT / CLICK THE
BUTTON WOULD YOU LIKE  TO DO

"Bob T" wrote:

> I'd like to make a cell change color or the cell's font change color as the
> number in the cell changes from negative to positive or vice versa?

----------


## RODY

HI BOB TRY USING CONDITIONAL FORMATING GO TO TOOLS MENU / CONDITIONAL
FORMATING /  CELL VALUE IS / NOT EQUAL TO / TYPE 0 / CLICK FORMAT / CLICK THE
BUTTON WOULD YOU LIKE  TO DO

"Bob T" wrote:

> I'd like to make a cell change color or the cell's font change color as the
> number in the cell changes from negative to positive or vice versa?

----------


## RODY

HI BOB TRY USING CONDITIONAL FORMATING GO TO TOOLS MENU / CONDITIONAL
FORMATING /  CELL VALUE IS / NOT EQUAL TO / TYPE 0 / CLICK FORMAT / CLICK THE
BUTTON WOULD YOU LIKE  TO DO

"Bob T" wrote:

> I'd like to make a cell change color or the cell's font change color as the
> number in the cell changes from negative to positive or vice versa?

----------


## RODY

HI BOB TRY USING CONDITIONAL FORMATING GO TO TOOLS MENU / CONDITIONAL
FORMATING /  CELL VALUE IS / NOT EQUAL TO / TYPE 0 / CLICK FORMAT / CLICK THE
BUTTON WOULD YOU LIKE  TO DO

"Bob T" wrote:

> I'd like to make a cell change color or the cell's font change color as the
> number in the cell changes from negative to positive or vice versa?

----------


## RODY

HI BOB TRY USING CONDITIONAL FORMATING GO TO TOOLS MENU / CONDITIONAL
FORMATING /  CELL VALUE IS / NOT EQUAL TO / TYPE 0 / CLICK FORMAT / CLICK THE
BUTTON WOULD YOU LIKE  TO DO

"Bob T" wrote:

> I'd like to make a cell change color or the cell's font change color as the
> number in the cell changes from negative to positive or vice versa?

----------


## RODY

HI BOB TRY USING CONDITIONAL FORMATING GO TO TOOLS MENU / CONDITIONAL
FORMATING /  CELL VALUE IS / NOT EQUAL TO / TYPE 0 / CLICK FORMAT / CLICK THE
BUTTON WOULD YOU LIKE  TO DO

"Bob T" wrote:

> I'd like to make a cell change color or the cell's font change color as the
> number in the cell changes from negative to positive or vice versa?

----------


## RODY

HI BOB TRY USING CONDITIONAL FORMATING GO TO TOOLS MENU / CONDITIONAL
FORMATING /  CELL VALUE IS / NOT EQUAL TO / TYPE 0 / CLICK FORMAT / CLICK THE
BUTTON WOULD YOU LIKE  TO DO

"Bob T" wrote:

> I'd like to make a cell change color or the cell's font change color as the
> number in the cell changes from negative to positive or vice versa?

----------


## RODY

HI BOB TRY USING CONDITIONAL FORMATING GO TO TOOLS MENU / CONDITIONAL
FORMATING /  CELL VALUE IS / NOT EQUAL TO / TYPE 0 / CLICK FORMAT / CLICK THE
BUTTON WOULD YOU LIKE  TO DO

"Bob T" wrote:

> I'd like to make a cell change color or the cell's font change color as the
> number in the cell changes from negative to positive or vice versa?

----------


## RODY

HI BOB TRY USING CONDITIONAL FORMATING GO TO TOOLS MENU / CONDITIONAL
FORMATING /  CELL VALUE IS / NOT EQUAL TO / TYPE 0 / CLICK FORMAT / CLICK THE
BUTTON WOULD YOU LIKE  TO DO

"Bob T" wrote:

> I'd like to make a cell change color or the cell's font change color as the
> number in the cell changes from negative to positive or vice versa?

----------


## RODY

HI BOB TRY USING CONDITIONAL FORMATING GO TO TOOLS MENU / CONDITIONAL
FORMATING /  CELL VALUE IS / NOT EQUAL TO / TYPE 0 / CLICK FORMAT / CLICK THE
BUTTON WOULD YOU LIKE  TO DO

"Bob T" wrote:

> I'd like to make a cell change color or the cell's font change color as the
> number in the cell changes from negative to positive or vice versa?

----------


## RODY

HI BOB TRY USING CONDITIONAL FORMATING GO TO TOOLS MENU / CONDITIONAL
FORMATING /  CELL VALUE IS / NOT EQUAL TO / TYPE 0 / CLICK FORMAT / CLICK THE
BUTTON WOULD YOU LIKE  TO DO

"Bob T" wrote:

> I'd like to make a cell change color or the cell's font change color as the
> number in the cell changes from negative to positive or vice versa?

----------


## RODY

HI BOB TRY USING CONDITIONAL FORMATING GO TO TOOLS MENU / CONDITIONAL
FORMATING /  CELL VALUE IS / NOT EQUAL TO / TYPE 0 / CLICK FORMAT / CLICK THE
BUTTON WOULD YOU LIKE  TO DO

"Bob T" wrote:

> I'd like to make a cell change color or the cell's font change color as the
> number in the cell changes from negative to positive or vice versa?

----------


## RODY

HI BOB TRY USING CONDITIONAL FORMATING GO TO TOOLS MENU / CONDITIONAL
FORMATING /  CELL VALUE IS / NOT EQUAL TO / TYPE 0 / CLICK FORMAT / CLICK THE
BUTTON WOULD YOU LIKE  TO DO

"Bob T" wrote:

> I'd like to make a cell change color or the cell's font change color as the
> number in the cell changes from negative to positive or vice versa?

----------


## RODY

HI BOB TRY USING CONDITIONAL FORMATING GO TO TOOLS MENU / CONDITIONAL
FORMATING /  CELL VALUE IS / NOT EQUAL TO / TYPE 0 / CLICK FORMAT / CLICK THE
BUTTON WOULD YOU LIKE  TO DO

"Bob T" wrote:

> I'd like to make a cell change color or the cell's font change color as the
> number in the cell changes from negative to positive or vice versa?

----------


## RODY

HI BOB TRY USING CONDITIONAL FORMATING GO TO TOOLS MENU / CONDITIONAL
FORMATING /  CELL VALUE IS / NOT EQUAL TO / TYPE 0 / CLICK FORMAT / CLICK THE
BUTTON WOULD YOU LIKE  TO DO

"Bob T" wrote:

> I'd like to make a cell change color or the cell's font change color as the
> number in the cell changes from negative to positive or vice versa?

----------


## RODY

HI BOB TRY USING CONDITIONAL FORMATING GO TO TOOLS MENU / CONDITIONAL
FORMATING /  CELL VALUE IS / NOT EQUAL TO / TYPE 0 / CLICK FORMAT / CLICK THE
BUTTON WOULD YOU LIKE  TO DO

"Bob T" wrote:

> I'd like to make a cell change color or the cell's font change color as the
> number in the cell changes from negative to positive or vice versa?

----------


## RODY

HI BOB TRY USING CONDITIONAL FORMATING GO TO TOOLS MENU / CONDITIONAL
FORMATING /  CELL VALUE IS / NOT EQUAL TO / TYPE 0 / CLICK FORMAT / CLICK THE
BUTTON WOULD YOU LIKE  TO DO

"Bob T" wrote:

> I'd like to make a cell change color or the cell's font change color as the
> number in the cell changes from negative to positive or vice versa?

----------

