Google Groups no longer supports new Usenet posts or subscriptions. Historical content remains viewable.
Dismiss

How To Detect A Negative Number In A Cell

11,837 views
Skip to first unread message

Minitman

unread,
Mar 13, 2008, 4:38:32 AM3/13/08
to
Greetings,

I am trying to detect whether the value in cell M for each row has a
negative number. If it does I need to change the text color for that
row to red.

Any help, insights or links would be greatly appreciated.

-Minitman

Stefi

unread,
Mar 13, 2008, 4:54:00 AM3/13/08
to
Select column M>Cell formatting>Category:Number>Select one of red patterns
for negative numbers
Regards,
Stefi


„Minitman” ezt írta:

David Biddulph

unread,
Mar 13, 2008, 7:10:16 AM3/13/08
to
If the OP wants to change the text colour for the whole row, rather than
just for that cell, then it'll need Conditional Formatting.
Formula Is/ =$M1<0
--
David Biddulph

"Stefi" <St...@discussions.microsoft.com> wrote in message
news:66C172A6-CC0A-4650...@microsoft.com...

Minitman

unread,
Mar 13, 2008, 9:20:52 AM3/13/08
to
Hey Stefi,

Thanks for the reply.

Since I need the entire row to change, cell formatting won't work. my
problem seems to be in the formula I tried to work in Conditional
Format:

=$M2<0

I can't get this formula to work in a test cell or in CF:

=IF($M2<0,"NEGATIVE","POSITIVE")

I do appreciate the attempt.

-Minitman

Minitman

unread,
Mar 13, 2008, 9:51:53 AM3/13/08
to
Hey David,

Thanks for the reply.

Nice catch on the whole row.

There seems to be a problem with that formula (=$M2<0)

That formula is what I tried at first in CF. I know how to get the
color I need if the formula is working. That formula seems to be the
culprit. So to test the formula, I moved back to the sheet and chose
a column to test the formula. In the first row (row 2), I placed:

=IF($M2<0,"NEGATIVE","POSITIVE")

I then copied it down for 10 rows (it automatically advanced the row
number), I paced both positive and negative numbers (along with a
zero in one row for testing) in the test rows M column.

They all came back "POSITIVE" which is to say they all returned FALSE.

Any idea as to what is wrong with this formula?

-Minitman

David Biddulph

unread,
Mar 13, 2008, 11:11:17 AM3/13/08
to
It sounds as if you've got text, rather than numbers, in your column M.
To convert to numbers, try copying a blank cell, then select column M and
use Edit/ Paste Special/ Add.
--
David Biddulph

"Minitman" <st...@minitmaidsofaustin.com> wrote in message
news:miait31rqsq3lfpsa...@4ax.com...

Minitman

unread,
Mar 13, 2008, 11:30:45 AM3/13/08
to
Hey David,

Thanks for the reply.

That was the problem.

Thank you very much.

-Minitman

David Biddulph

unread,
Mar 13, 2008, 12:12:17 PM3/13/08
to
Glad it worked for you.
--
David Biddulph

"Minitman" <st...@minitmaidsofaustin.com> wrote in message

news:eghit3d3ntm8inr8m...@4ax.com...

0 new messages