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

H

Husker87

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....
 
B

Bob Phillips

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)
 
R

RagDyer

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!
==============================================
 
H

Husker87

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?
 
H

Husker87

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 said:
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 said:
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....
Click to expand...
Click to expand...
 
T

Temp

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

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

RagDyeR

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!
=====================================================

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 said:
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 said:
OK... here's the question. I have a cell that I input a number into.
Click to expand...
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....
Click to expand...
Click to expand...
 
H

Husker87

Perfect . Thanks Much. Have a good weekend.

RagDyeR said:
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!
=====================================================

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 said:
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 said:
OK... here's the question. I have a cell that I input a number into.
Click to expand...
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....
Click to expand...
Click to expand...
Click to expand...
 
R

RagDyer

Appreciate the feed-back.
--
Regards,

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


Husker87 said:
Perfect . Thanks Much. Have a good weekend.

RagDyeR said:
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!
=====================================================

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 said:
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!
==============================================


OK... here's the question. I have a cell that I input a number
Click to expand...
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....
Click to expand...
Click to expand...
Click to expand...
 
Top