Excel - Conditional Formatting - Number Format

Asked By AdmiralAJ on 13-Mar-09 05:20 AM
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 replied on 11-Mar-09 06:10 PM
Are you sure that cell B1 was active when you applied the rule?

--
__________________________________
HTH

Bob
AJ Master replied on 13-Mar-09 05:20 AM
d
n
s

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 replied on 11-Mar-09 08:01 PM
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
AJ Master replied on 13-Mar-09 05:22 AM
=3D1,
.
l

Hi Bob,

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

Rule
Format                               Applies To
---------------------------------------------------------------------------=
---------------------------------------------------------------------------
Formula: "=3Da1=3D1"              3871800.00%                     =3D$b$1

Does that look wrong?
Bob Phillips replied on 12-Mar-09 05:44 PM
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
------------------------------------------------------------------------------------------------------------------------------------------------------
Formula: "=a1=1"              3871800.00%                     =$b$1

Does that look wrong?
AJ Master replied on 13-Mar-09 05:22 AM
o
1=3D1,
ge
...
ell
g.
s
s
ed
en
ed
as
s To
---------------------------------------------------------------------------=
--
=A0 =A0 =A0 =A0 =A0 =A0 =A0 =3D$b$1

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 replied on 12-Mar-09 06:16 PM
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???
AJ Master replied on 19-Mar-09 09:01 PM
to
.
..
om...
to
his
at
as
ared
l
---------------------------------------------------------------------------=
----
e

Bob,

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