Conditional formatting for changing data

M

Mariliz

I hope this makes sense. I just got Excel 2007. I have conditional
formatting that will change the format of a column with a currency value
based on another column's text saying what the currency is. (IE, column A is
currency type, column B is value. Formatting rule for B1 says =A1="dollar"
format should be $1.00, =A1="euro" format should be in euros, etc.)

It works fine, once, when I enter the currency type. But if I try to change
it, it doesn't work anymore. Unless I open the rules again and click ok;
then it works again, and will once more if I change the currency. But it
won't do it more than once. I can't figure out why or what I'm doing wrong.
All my other conditional formats work, no matter how much I change the data.
But this one just won't....
 
S

Sheeloo

i tried what you said and it works fine for me.

Here is what I did
Entered Dollar in A1, Euro in A2
Entered some numbers in B1, B2
Selected Col B
Chose Conditional Formatting
For Condition 1 entered
=A1="Dollar" and chose formatting as Custom|"Dollar" 0.00
For Condition 2 entered
=A1="Euro" and chose formatting as Custom|"Euro" 0.00

Got the appropriate formatting in B1 and B2
Now if I change A1 (or A2) to Dollar or Euro B1 (or B2) changes accordingly.

What are you doing differently?
 
M

Mariliz

I'm not sure--I have the currency choices in a drop down (so that you can
only enter a valid currency type.) And like I said, it works the first time
or two, but it stops working after that. I have other cells that convert the
total from the original currency to dollars, based on the drop down and those
work fine no matter how often I change the drop down. But if, for example, I
have dollar it'll format in dollars. I change to euro, sometimes it changes
the format to euro. I change back to dollar and it doesn't do anything to
the format. I'm totally stumped--all the other conditional formatting I have
in the workbook works properly.
 
S

Sheeloo

If drop down is in cell A1 and you are referencing it to format B1 then B1
should change every time you pick a value in A1.
Try
=TRIM(A1)="dollar" to make sure that picklist does not have extra
characters.. but then it should not work even once.

Can you send the file to me?
add @hotmail.com to to_sheeloo to get my id
(yes to_ is part of the id)

Do indicate the Excel version...
 
M

Mariliz

Unfortunately, I can't send the file. I was playing around a bit more and
have discovered that it's only the conditional formatting when the format is
a currency that isn't working. If i have the format change the color of the
cell, for example, it works no matter how much I change it back and forth.
But the currency format won't change with it.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top