I am working on a spreadsheet where I am dealing with different medicines. I
have one column set up where it subtracts the actual cost from how much the
insurance pays. When the number is negative I have the cell formatted where
it becomes red with parenthesis.

My question is how can I set it up where the medicine also becomes red, when
the amount is a negative number besides manually making it red? Almost like
conditional formatting, but there is no condition. If the amount is red, I
want the medicine to reflect that as well.

Select the column with the medicines and Conditionally Format it so that the
it tests the column with the subtractions in it for being less than zero.
Something like this where I'm assuming (because you didn't tell us) that the
subtraction column is Column D. Assuming the active cell in the medicine
column is A1, use this for the conditional formula...

=D1<0

(Just make sure the row you specify in the subtraction column is the same as
the row in the medicine column).

--
Rick (MVP - Excel)


"Té" wrote in message
...
I am working on a spreadsheet where I am dealing with different medicines.
I
have one column set up where it subtracts the actual cost from how much
the
insurance pays. When the number is negative I have the cell formatted
where
it becomes red with parenthesis.

My question is how can I set it up where the medicine also becomes red,
when
the amount is a negative number besides manually making it red? Almost
like
conditional formatting, but there is no condition. If the amount is red,
I
want the medicine to reflect that as well.

hi
a cell with a negative number is the condition.
lets say that C2 is the cell with the formula that subtracts cost from
insurance. lets say medicine name is in D2. in D2, add this conditional
formating......
formula is....=c2<0.......pic your format(font = red?)

you can copy then paste special formats down as far as you need.

regards
FSt1

"Té" wrote:

I am working on a spreadsheet where I am dealing with different medicines. I
have one column set up where it subtracts the actual cost from how much the
insurance pays. When the number is negative I have the cell formatted where
it becomes red with parenthesis.

My question is how can I set it up where the medicine also becomes red, when
the amount is a negative number besides manually making it red? Almost like
conditional formatting, but there is no condition. If the amount is red, I
want the medicine to reflect that as well.

With the conditional formatting is it equal to? My medicine is in a1, amount
of medicine is in b1, insurance cost is in c1, and the formula =b1-c1 is in
d1. I tried using the conditional formatting on a1, but nothing happened.

"FSt1" wrote:

hi
a cell with a negative number is the condition.
lets say that C2 is the cell with the formula that subtracts cost from
insurance. lets say medicine name is in D2. in D2, add this conditional
formating......
formula is....=c2<0.......pic your format(font = red?)

you can copy then paste special formats down as far as you need.

regards
FSt1

"Té" wrote:

I am working on a spreadsheet where I am dealing with different medicines. I
have one column set up where it subtracts the actual cost from how much the
insurance pays. When the number is negative I have the cell formatted where
it becomes red with parenthesis.

My question is how can I set it up where the medicine also becomes red, when
the amount is a negative number besides manually making it red? Almost like
conditional formatting, but there is no condition. If the amount is red, I
want the medicine to reflect that as well.

Whichever column would have the negative value.

I assume it would be column D due to the ='B1-C1

Rick posted =$D1<0

Select Column A and enter the above formula in FormatCFFormula is:


Gord Dibben MS Excel MVP

On Mon, 15 Dec 2008 17:00:01 -0800, Té wrote:

With the conditional formatting is it equal to? My medicine is in a1, amount
of medicine is in b1, insurance cost is in c1, and the formula =b1-c1 is in
d1. I tried using the conditional formatting on a1, but nothing happened.

"FSt1" wrote:

hi
a cell with a negative number is the condition.
lets say that C2 is the cell with the formula that subtracts cost from
insurance. lets say medicine name is in D2. in D2, add this conditional
formating......
formula is....=c2<0.......pic your format(font = red?)

you can copy then paste special formats down as far as you need.

regards
FSt1

"Té" wrote:

I am working on a spreadsheet where I am dealing with different medicines. I
have one column set up where it subtracts the actual cost from how much the
insurance pays. When the number is negative I have the cell formatted where
it becomes red with parenthesis.

My question is how can I set it up where the medicine also becomes red, when
the amount is a negative number besides manually making it red? Almost like
conditional formatting, but there is no condition. If the amount is red, I
want the medicine to reflect that as well.

Thank you! That worked!!!

"Gord Dibben" wrote:

Whichever column would have the negative value.

I assume it would be column D due to the ='B1-C1

Rick posted =$D1<0

Select Column A and enter the above formula in FormatCFFormula is:


Gord Dibben MS Excel MVP

On Mon, 15 Dec 2008 17:00:01 -0800, Té wrote:

With the conditional formatting is it equal to? My medicine is in a1, amount
of medicine is in b1, insurance cost is in c1, and the formula =b1-c1 is in
d1. I tried using the conditional formatting on a1, but nothing happened.

"FSt1" wrote:

hi
a cell with a negative number is the condition.
lets say that C2 is the cell with the formula that subtracts cost from
insurance. lets say medicine name is in D2. in D2, add this conditional
formating......
formula is....=c2<0.......pic your format(font = red?)

you can copy then paste special formats down as far as you need.

regards
FSt1

"Té" wrote:

I am working on a spreadsheet where I am dealing with different medicines. I
have one column set up where it subtracts the actual cost from how much the
insurance pays. When the number is negative I have the cell formatted where
it becomes red with parenthesis.

My question is how can I set it up where the medicine also becomes red, when
the amount is a negative number besides manually making it red? Almost like
conditional formatting, but there is no condition. If the amount is red, I
want the medicine to reflect that as well.

Is there a way I can set up all of column A without manually clicking on each
individual cell?

"Gord Dibben" wrote:

Whichever column would have the negative value.

I assume it would be column D due to the ='B1-C1

Rick posted =$D1<0

Select Column A and enter the above formula in FormatCFFormula is:


Gord Dibben MS Excel MVP

On Mon, 15 Dec 2008 17:00:01 -0800, Té wrote:

With the conditional formatting is it equal to? My medicine is in a1, amount
of medicine is in b1, insurance cost is in c1, and the formula =b1-c1 is in
d1. I tried using the conditional formatting on a1, but nothing happened.

"FSt1" wrote:

hi
a cell with a negative number is the condition.
lets say that C2 is the cell with the formula that subtracts cost from
insurance. lets say medicine name is in D2. in D2, add this conditional
formating......
formula is....=c2<0.......pic your format(font = red?)

you can copy then paste special formats down as far as you need.

regards
FSt1

"Té" wrote:

I am working on a spreadsheet where I am dealing with different medicines. I
have one column set up where it subtracts the actual cost from how much the
insurance pays. When the number is negative I have the cell formatted where
it becomes red with parenthesis.

My question is how can I set it up where the medicine also becomes red, when
the amount is a negative number besides manually making it red? Almost like
conditional formatting, but there is no condition. If the amount is red, I
want the medicine to reflect that as well.

You missed it when I said it and you missed it in Gord's post.... first
thing mentioned was "Select Column A" (that is, select the entire column
before going to the conditional format). What you are doing is setting the
conditional format for the active cell in the selection... Excel will take
care of applying the formula to the rest of the cells in the selection for
you.

--
Rick (MVP - Excel)


"Té" wrote in message
...
Is there a way I can set up all of column A without manually clicking on
each
individual cell?

"Gord Dibben" wrote:

Whichever column would have the negative value.

I assume it would be column D due to the ='B1-C1

Rick posted =$D1<0

Select Column A and enter the above formula in FormatCFFormula is:


Gord Dibben MS Excel MVP

On Mon, 15 Dec 2008 17:00:01 -0800, Té
wrote:

With the conditional formatting is it equal to? My medicine is in a1,
amount
of medicine is in b1, insurance cost is in c1, and the formula =b1-c1 is
in
d1. I tried using the conditional formatting on a1, but nothing
happened.

"FSt1" wrote:

hi
a cell with a negative number is the condition.
lets say that C2 is the cell with the formula that subtracts cost
from
insurance. lets say medicine name is in D2. in D2, add this
conditional
formating......
formula is....=c2<0.......pic your format(font = red?)

you can copy then paste special formats down as far as you need.

regards
FSt1

"Té" wrote:

I am working on a spreadsheet where I am dealing with different
medicines. I
have one column set up where it subtracts the actual cost from how
much the
insurance pays. When the number is negative I have the cell
formatted where
it becomes red with parenthesis.

My question is how can I set it up where the medicine also becomes
red, when
the amount is a negative number besides manually making it red?
Almost like
conditional formatting, but there is no condition. If the amount is
red, I
want the medicine to reflect that as well.

Yes.

As I posted......................

Select Column A or any part of and enter the above formula in
FormatCFFormula is:

=$D1<0

The $ sign fixes the D column as the source but the row reference will
change for each cell.


Gord

On Tue, 16 Dec 2008 13:01:01 -0800, Té wrote:

Is there a way I can set up all of column A without manually clicking on each
individual cell?

"Gord Dibben" wrote:

Whichever column would have the negative value.

I assume it would be column D due to the ='B1-C1

Rick posted =$D1<0

Select Column A and enter the above formula in FormatCFFormula is:


Gord Dibben MS Excel MVP

On Mon, 15 Dec 2008 17:00:01 -0800, Té wrote:

With the conditional formatting is it equal to? My medicine is in a1, amount
of medicine is in b1, insurance cost is in c1, and the formula =b1-c1 is in
d1. I tried using the conditional formatting on a1, but nothing happened.

"FSt1" wrote:

hi
a cell with a negative number is the condition.
lets say that C2 is the cell with the formula that subtracts cost from
insurance. lets say medicine name is in D2. in D2, add this conditional
formating......
formula is....=c2<0.......pic your format(font = red?)

you can copy then paste special formats down as far as you need.

regards
FSt1

"Té" wrote:

I am working on a spreadsheet where I am dealing with different medicines. I
have one column set up where it subtracts the actual cost from how much the
insurance pays. When the number is negative I have the cell formatted where
it becomes red with parenthesis.

My question is how can I set it up where the medicine also becomes red, when
the amount is a negative number besides manually making it red? Almost like
conditional formatting, but there is no condition. If the amount is red, I
want the medicine to reflect that as well.