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

D

dave55

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
 
J

Jason Morin

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
 
M

Max

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
 
Top