How do I change the format of a cell based on what I input?
 

OK... here's the question. I have a cell that I input a number into. If I
input a number less than 1, I want the cell to be formated with a percentage
sign. If I input a number greater than 1, I want it to show just as a number
(with no percentage sign).

I would prefer the answer NOT include a macro as this worksheet will be used
on older computers that have a hard time running macros. If it can't be done
without a macro then a macro will be OK. Any thoughts? and thanks very
much....

Conditional formatting won't affect the numberformat so you need code

Private Sub Worksheet_Change(ByVal Target As Range)
Const WS_RANGE As String = "H1:H10"

On Error GoTo ws_exit:
Application.EnableEvents = False
If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then
With Target
If .Value < 1 Then
.NumberFormat = "0%"
Else
.NumberFormat = "General"
End If
End With
End If

ws_exit:
Application.EnableEvents = True
End Sub

'This is worksheet event code, which means that it needs to be
'placed in the appropriate worksheet code module, not a standard
'code module. To do this, right-click on the sheet tab, select
'the View Code option from the menu, and paste the code in.

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Husker87" wrote in message
...
OK... here's the question. I have a cell that I input a number into. If
I
input a number less than 1, I want the cell to be formated with a
percentage
sign. If I input a number greater than 1, I want it to show just as a
number
(with no percentage sign).

I would prefer the answer NOT include a macro as this worksheet will be
used
on older computers that have a hard time running macros. If it can't be
done
without a macro then a macro will be OK. Any thoughts? and thanks very
much....

You can try this custom format:

[=1]#,###.00;[<1]#.0%;GeneraÂ*l

--
HTH,

RD
==============================================
Please keep all correspondence within the Group, so all may benefit!
==============================================


"Husker87" wrote in message
...
OK... here's the question. I have a cell that I input a number into. If
I
input a number less than 1, I want the cell to be formated with a
percentage
sign. If I input a number greater than 1, I want it to show just as a
number
(with no percentage sign).

I would prefer the answer NOT include a macro as this worksheet will be
used
on older computers that have a hard time running macros. If it can't be
done
without a macro then a macro will be OK. Any thoughts? and thanks very
much....

Thanks Bob... work well. One question... when you enter a number greater
than 1 in a cell that previously had a number less than 1 (with a percentage
sign) it automatically moves the decimal over two spaces... (1,200 shows up
as 12.00) then when you enter it again it enters it appears correctly.
(1,200 is 1,200) Anyway to make it so you can go smoothly back and forth
between the percent sign and a number greater than one?

"Bob Phillips" wrote:

Conditional formatting won't affect the numberformat so you need code

Private Sub Worksheet_Change(ByVal Target As Range)
Const WS_RANGE As String = "H1:H10"

On Error GoTo ws_exit:
Application.EnableEvents = False
If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then
With Target
If .Value < 1 Then
.NumberFormat = "0%"
Else
.NumberFormat = "General"
End If
End With
End If

ws_exit:
Application.EnableEvents = True
End Sub

'This is worksheet event code, which means that it needs to be
'placed in the appropriate worksheet code module, not a standard
'code module. To do this, right-click on the sheet tab, select
'the View Code option from the menu, and paste the code in.

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Husker87" wrote in message
...
OK... here's the question. I have a cell that I input a number into. If
I
input a number less than 1, I want the cell to be formated with a
percentage
sign. If I input a number greater than 1, I want it to show just as a
number
(with no percentage sign).

I would prefer the answer NOT include a macro as this worksheet will be
used
on older computers that have a hard time running macros. If it can't be
done
without a macro then a macro will be OK. Any thoughts? and thanks very
much....

Works well with the number less than 1 but maybe I'm doing something wrong...
it doesn't want to take numbers greater than 1 without the % sign. I tryed
14 and it comes up "14.0%" Any ideas? Thanks for lookng at it....

"RagDyer" wrote:

You can try this custom format:

[=1]#,###.00;[<1]#.0%;GeneraÂ*l

--
HTH,

RD
==============================================
Please keep all correspondence within the Group, so all may benefit!
==============================================


"Husker87" wrote in message
...
OK... here's the question. I have a cell that I input a number into. If
I
input a number less than 1, I want the cell to be formated with a
percentage
sign. If I input a number greater than 1, I want it to show just as a
number
(with no percentage sign).

I would prefer the answer NOT include a macro as this worksheet will be
used
on older computers that have a hard time running macros. If it can't be
done
without a macro then a macro will be OK. Any thoughts? and thanks very
much....

Put your number in cell A1 then this formula in cell B1:

=TEXT(A1,"#,###"&IF(A1<1,"%",""))

Sorry, forgot to mention:

<Tools <Options <Edit tab,

And *UNCHECK*

"Enable Auto Percent Entry".

This custom format doesn't seem to work on versions without this option
(XL97).
--

HTH,

RD
================================================== ===
Please keep all correspondence within the Group, so all may benefit!
================================================== ===

"Husker87" wrote in message
...
Works well with the number less than 1 but maybe I'm doing something
wrong...
it doesn't want to take numbers greater than 1 without the % sign. I tryed
14 and it comes up "14.0%" Any ideas? Thanks for lookng at it....

"RagDyer" wrote:

You can try this custom format:

[=1]#,###.00;[<1]#.0%;Genera*l

--
HTH,

RD
==============================================
Please keep all correspondence within the Group, so all may benefit!
==============================================


"Husker87" wrote in message
...
OK... here's the question. I have a cell that I input a number into.
If
I
input a number less than 1, I want the cell to be formated with a
percentage
sign. If I input a number greater than 1, I want it to show just as a
number
(with no percentage sign).

I would prefer the answer NOT include a macro as this worksheet will be
used
on older computers that have a hard time running macros. If it can't be
done
without a macro then a macro will be OK. Any thoughts? and thanks very
much....

Perfect . Thanks Much. Have a good weekend.

"RagDyeR" wrote:

Sorry, forgot to mention:

<Tools <Options <Edit tab,

And *UNCHECK*

"Enable Auto Percent Entry".

This custom format doesn't seem to work on versions without this option
(XL97).
--

HTH,

RD
================================================== ===
Please keep all correspondence within the Group, so all may benefit!
================================================== ===

"Husker87" wrote in message
...
Works well with the number less than 1 but maybe I'm doing something
wrong...
it doesn't want to take numbers greater than 1 without the % sign. I tryed
14 and it comes up "14.0%" Any ideas? Thanks for lookng at it....

"RagDyer" wrote:

You can try this custom format:

[=1]#,###.00;[<1]#.0%;GeneraÂ*l

--
HTH,

RD
==============================================
Please keep all correspondence within the Group, so all may benefit!
==============================================


"Husker87" wrote in message
...
OK... here's the question. I have a cell that I input a number into.
If
I
input a number less than 1, I want the cell to be formated with a
percentage
sign. If I input a number greater than 1, I want it to show just as a
number
(with no percentage sign).

I would prefer the answer NOT include a macro as this worksheet will be
used
on older computers that have a hard time running macros. If it can't be
done
without a macro then a macro will be OK. Any thoughts? and thanks very
much....

Appreciate the feed-back.
--
Regards,

RD
==============================================
Please keep all correspondence within the Group, so all may benefit!
==============================================


"Husker87" wrote in message
...
Perfect . Thanks Much. Have a good weekend.

"RagDyeR" wrote:

Sorry, forgot to mention:

<Tools <Options <Edit tab,

And *UNCHECK*

"Enable Auto Percent Entry".

This custom format doesn't seem to work on versions without this option
(XL97).
--

HTH,

RD
================================================== ===
Please keep all correspondence within the Group, so all may benefit!
================================================== ===

"Husker87" wrote in message
...
Works well with the number less than 1 but maybe I'm doing something
wrong...
it doesn't want to take numbers greater than 1 without the % sign. I
tryed
14 and it comes up "14.0%" Any ideas? Thanks for lookng at it....

"RagDyer" wrote:

You can try this custom format:

[=1]#,###.00;[<1]#.0%;GeneraÂ*l

--
HTH,

RD
==============================================
Please keep all correspondence within the Group, so all may benefit!
==============================================


"Husker87" wrote in message
...
OK... here's the question. I have a cell that I input a number
into.
If
I
input a number less than 1, I want the cell to be formated with a
percentage
sign. If I input a number greater than 1, I want it to show just as
a
number
(with no percentage sign).

I would prefer the answer NOT include a macro as this worksheet will
be
used
on older computers that have a hard time running macros. If it
can't be
done
without a macro then a macro will be OK. Any thoughts? and thanks
very
much....