Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Husker87
 
Posts: n/a
Default 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....
  #2   Report Post  
Bob Phillips
 
Posts: n/a
Default

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



  #3   Report Post  
RagDyer
 
Posts: n/a
Default

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


  #4   Report Post  
Husker87
 
Posts: n/a
Default

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




  #5   Report Post  
Husker87
 
Posts: n/a
Default

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





  #6   Report Post  
Temp
 
Posts: n/a
Default

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

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

  #7   Report Post  
RagDyeR
 
Posts: n/a
Default

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





  #8   Report Post  
Husker87
 
Posts: n/a
Default

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






  #9   Report Post  
RagDyer
 
Posts: n/a
Default

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






Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Format text in one cell based on value in another cell Liz C Excel Worksheet Functions 2 June 23rd 05 07:48 PM
Can I format a cell in excel based on a list of about 20 items? arkansooner Excel Discussion (Misc queries) 1 June 17th 05 02:38 PM
Copy cell format to cell on another worksht and update automatical kevinm Excel Worksheet Functions 21 May 19th 05 11:07 AM
Format cell in column B based on value in the next cell (column c) Nicole Excel Discussion (Misc queries) 7 May 18th 05 10:19 PM
How do I change the value in cell based on a future date John W Excel Discussion (Misc queries) 2 December 21st 04 02:27 AM


All times are GMT +1. The time now is 09:27 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"