Formatting

[Té]

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.

 

[Rick Rothstein]

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).

 

[FSt1]

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é]

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.

 

[Gord Dibben]

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 Format>CF>Formula is:


Gord Dibben MS Excel MVP

 

[Té]

Thank you! That worked!!!

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 Format>CF>Formula is:


Gord Dibben MS Excel MVP

 

[Té]

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

 

[Rick Rothstein]

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.

 

[Gord Dibben]

Yes.

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

Select Column A or any part of and enter the above formula in
Format>CF>Formula is:

=$D1<0

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


Gord