Conditional Formatting - Number Format

[AdmiralAJ]

I am using Excel 2007 and have tried using conditional formatting to
format a cell's number format based on a condition in an adjacent cell
but its not working and would love any input on what I'm doing wrong.

Here's what I'm doing:

A1 B1
1 or 0 2 conditional formatting rule(s):

I'm choosing:
Conditional Formatting--->New Rule--->"Use a formula to determine
which cells to format" ---> (then in the description box I type this
formula) =A1=1. If this condition is true format as %.

I then create a 2nd rule following the same steps above except that
the formula now reads =A1=0. If this condition is true format as
Currency.

When I do this is always shows % formatting even if A1=0. I cleared
all and then tried again, didn't work. Then I cleared all again, then
I formatted the cell as % and then just adding the 2nd rule mentioned
above and I get the same problem. I also tried formatting the cell as
currency and then adding rule 1 only and still the same problem.

Any insight would be appreciated...AJ

 

[Bob Phillips]

Are you sure that cell B1 was active when you applied the rule?

 

[AJ Master]

Are you sure that cell B1 was active when you applied the rule?

--
__________________________________
HTH

Bob

Hi Bob,

Yes, I checked. I just looked under "manage rules" and the formatting
applies to that cell. But still not working.


AJ

 

[Bob Phillips]

But you have to have B1 active if you use a relative formula, like =A1=1,
otherwise Excel adjusts the formula. What does the formula show in Manage
Rules when you have B1 as the active cell.

--
__________________________________
HTH

Bob

Are you sure that cell B1 was active when you applied the rule?

--
__________________________________
HTH

Bob

Hi Bob,

Yes, I checked. I just looked under "manage rules" and the formatting
applies to that cell. But still not working.


AJ

 

[AJ Master]

But you have to have B1 active if you use a relative formula, like =A1=1,
otherwise Excel adjusts the formula. What does the formula show in Manage
Rules when  you  have B1 as the active cell.

--
__________________________________
HTH

Bob




Hi Bob,

Yes, I checked.  I just looked under "manage rules" and the formatting
applies to that cell.  But still not working.

AJ

Hi Bob,

When I go to "manage rules" this is what it shows:

Rule
Format Applies To

 

[Bob Phillips]

I have just tried this again, and there is a problem.

If you change the value of A1 to the second format condition, then the
format of B1 does not change. Go and edit B1, and it changes. Change A1 to
the first format condition, and the format of B1 changes automatically.

Is this what you get?

--
__________________________________
HTH

Bob

But you have to have B1 active if you use a relative formula, like =A1=1,
otherwise Excel adjusts the formula. What does the formula show in Manage
Rules when you have B1 as the active cell.

--
__________________________________
HTH

Bob




Hi Bob,

Yes, I checked. I just looked under "manage rules" and the formatting
applies to that cell. But still not working.

AJ

Hi Bob,

When I go to "manage rules" this is what it shows:

Rule
Format Applies To

 

[AJ Master]

I have just tried this again, and there is a problem.

If you change the value of A1 to the second format condition, then the
format of B1 does not change. Go and edit B1, and it changes. Change A1 to
the first format condition, and the format of B1 changes automatically.

Is this what you get?

--
__________________________________
HTH

Bob







Hi Bob,

When I go to "manage rules" this is what it shows:

Rule
Format                               Applies To

Nope that's not what I'm getting.

Here's what I'm doing. I have B1 formatted as currency. I've set the
conditional format as my previous post states. A1 is currently
condition 2 (i.e. 0). I enter a 3 in b1 and it formats as $3.00. I
then change A1 to condition 1 (i.e. 1) and B1 changes to 300%, which
is what I expect. Then I change A1 to condition 2, but B1 still says
300%. I delete the contents of B1 and then enter 3 again, but it
still formats it as 300% instead of $3.00???

 

[Bob Phillips]

Try Ctrl-Alt-F9 when it doesn't reformat, and see if it does then.

You look to be seeing exactly what I am seeing.

--
__________________________________
HTH

Bob

I have just tried this again, and there is a problem.

If you change the value of A1 to the second format condition, then the
format of B1 does not change. Go and edit B1, and it changes. Change A1 to
the first format condition, and the format of B1 changes automatically.

Is this what you get?

--
__________________________________
HTH

Bob







Hi Bob,

When I go to "manage rules" this is what it shows:

Rule
Format Applies To

Nope that's not what I'm getting.

Here's what I'm doing. I have B1 formatted as currency. I've set the
conditional format as my previous post states. A1 is currently
condition 2 (i.e. 0). I enter a 3 in b1 and it formats as $3.00. I
then change A1 to condition 1 (i.e. 1) and B1 changes to 300%, which
is what I expect. Then I change A1 to condition 2, but B1 still says
300%. I delete the contents of B1 and then enter 3 again, but it
still formats it as 300% instead of $3.00???

 

[AJ Master]

Try Ctrl-Alt-F9 when  it doesn't reformat, and see if it does then.

You look to be seeing exactly what I am seeing.

--
__________________________________
HTH

Bob










Nope that's not what I'm getting.

Here's what I'm doing.  I have B1 formatted as currency.  I've set the
conditional format as my previous post states.  A1 is currently
condition 2 (i.e. 0).  I enter a 3 in b1 and it formats as $3.00.  I
then change A1 to condition 1 (i.e. 1) and B1 changes to 300%, which
is what I expect.  Then I change A1 to condition 2, but B1 still says
300%.  I delete the contents of B1 and then enter 3 again, but it
still formats it as 300% instead of $3.00???

Bob,

That didn't seem to work either. Looks like I'll be coding a
worksheet change event.