How do I do conditional formatting on number formats not patterns.

Cell A1 is a drop down list that you select either "A" or "P". Cell B1 is a
cell where the user enters a number. If A1 is "A" I want the number in B1 to
show as an amount in $ whereas if A1 is "P" i want B1 to show as a percentage.

The conditional formatting option allows me to change fonts, borders, and
patterns but not the number format.

Any help would be appreciated

Right-click on the worksheet tab, go to View Code, and
paste in the code below. Press ALT+Q to close VBE.

Private Sub Worksheet_Change(ByVal Target As Range)
On Error GoTo ExitThisSub
If Not Intersect(Target, Me.[A1]) Is Nothing Then
Application.EnableEvents = False
With Target
If .Value = "A" Then
.Offset(0, 1).NumberFormat = "$#,##0.00"
Else
.Offset(0, 1).NumberFormat = "0.0%"
End If
End With
End If
ExitThisSub:
Application.EnableEvents = True
End Sub

---
HTH
Jason
Atlanta, GA

>-----Original Message-----
>Cell A1 is a drop down list that you select either "A"
or "P". Cell B1 is a
>cell where the user enters a number. If A1 is "A" I want
the number in B1 to
>show as an amount in $ whereas if A1 is "P" i want B1 to
show as a percentage.
>
>The conditional formatting option allows me to change
fonts, borders, and
>patterns but not the number format.
>
>Any help would be appreciated
>
>.
>

Just a formula play to tinker around with in the interim
(its probably not what you're after)

We could put in C1, something like:

=IF(A1="","Select an option in col
A",IF(A1="A",TEXT(B1,"$#,##0.00"),TEXT(B1,"0%")))

and copy C1 down

Col C will return the number entered in col B as text* in the display format
desired depending on the selection made in col A

*To enable downstream calcs refering to inputs made in col B, either point
direct at col B's values, or point at col C's values but include a "+0",
e.g.: =C1+0 to coerce the text in col C to real numbers

Do hang around for better insights from others to your post
--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <at>yahoo<dot>com
----
"dave55" <[email protected]> wrote in message
news:[email protected]...
> Cell A1 is a drop down list that you select either "A" or "P". Cell B1 is
a
> cell where the user enters a number. If A1 is "A" I want the number in B1
to
> show as an amount in $ whereas if A1 is "P" i want B1 to show as a
percentage.
>
> The conditional formatting option allows me to change fonts, borders, and
> patterns but not the number format.
>
> Any help would be appreciated
>