Conditional Formatting - Number Format

  • Thread starter Thread starter AdmiralAJ
  • Start date Start date
A

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
 
Are you sure that cell B1 was active when you applied the rule?

--
__________________________________
HTH

Bob
Click to expand...

Hi Bob,

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


AJ
 
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
Click to expand...

Hi Bob,

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


AJ
 
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
Click to expand...

Hi Bob,

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

Rule
Format Applies To
 
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
Click to expand...

Hi Bob,

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

Rule
Format Applies To
 
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
Click to expand...

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???
 
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
Click to expand...

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???
 
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???
Click to expand...

Bob,

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