Results 1 to 10 of 10
  1. #1
    5 Star Lounger Ruff_Hi's Avatar
    Join Date
    Feb 2001
    Location
    New York, New York, USA
    Posts
    768
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Change Format (Excel XL)

    Hi All,

    I have a similar question to that answered by Steve <!post=in this post,407977>in this post<!/post> but my formatting condition is based on another cell. For example, if Cell B2 = 1, then I want column D to be formatted as a percentage (with 1 decimal place). If, however, Cell B2 = 2, then I want column D to be formatted as a number (with 3 decimal places).
    (Location Australia, then UK, but now USA. Heart, outlook, attitude, etc always Australian)
    Quote: "All Happiness is the release of internal pressure"

  2. #2
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Change Format (Excel XL)

    How do you want column D formatted if B2 is anything other than 1 or 2?
    Legare Coleman

  3. #3
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Change Format (Excel XL)

    You could put the following code in the worksheet change event routing for the worksheet:

    Private Sub Worksheet_Change(ByVal Target As Range)
    If Intersect(Target, Range("B2")) Is Nothing Then Exit Sub
    If Range("B2").Value = 1 Then
    Range("D").NumberFormat = "0.0%"
    Else
    If Range("B2").Value = 2 Then
    Range("D").NumberFormat = "0.000"
    Else
    Range("D").NumberFormat = "General"
    End If
    End If
    End Sub

    Not having gotten an answer to my last post yet, this code will format column D in General format if B2 is anything other than 1 or 2.
    Legare Coleman

  4. #4
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Change Format (Excel XL)

    <P ID="edit" class=small>(Edited by sdckapr on 08-Jul-05 14:40. oops fixed my formatting reversed the 2 possibilites...)</P>You could add something like this in the worksheet object:

    <pre>Option Explicit
    Private Sub Worksheet_Change(ByVal Target As Excel.Range)
    Dim rng As Range
    Dim rCell As Range
    Set rCell = Range("B2")
    If Not Intersect(Target, rCell) Is Nothing Then
    Set rng = Range(Range("D1"), Range("D65536").End(xlUp))
    With Application
    .EnableEvents = False
    With rng
    Select Case rCell.Value
    Case Is = 1
    .NumberFormat = "0.0%"
    Case Is = 2
    .NumberFormat = "0.000"
    Case Else
    .NumberFormat = "General"
    End Select
    End With
    .EnableEvents = True
    End With
    End If
    Set rCell = Nothing
    Set rng = Nothing
    End Sub</pre>


    It will convert the cells to general if it is neither 1 nor 2 (change as desired)...

    Steve

  5. #5
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Change Format (Excel XL)

    I'm not completely sure what was wanted. Your code only formats cells down to the last one that currently has data. If additional data is added to the end of column D, it will not be formatted until cell B2 is changed again. If that is what is wanted, then that is fine.
    Legare Coleman

  6. #6
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Change Format (Excel XL)

    Yes that was mine intent. (I do not like formatting more than necessary since it sometimes affects the UsedRange...)

    I was not sure either what is desired. It can be modified with either code easily enough.including other values for B2.

    Steve

  7. #7
    5 Star Lounger Ruff_Hi's Avatar
    Join Date
    Feb 2001
    Location
    New York, New York, USA
    Posts
    768
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Change Format (Excel XL)

    Legare / Steve - thx for the replies and sorry for not seeing your earlier ones Legare. The cell / column was only to illustrate the concept.

    I was thinking of an excel in-cell formula something like conditional formating (or similar) rather than via VBA but it looks like the solution will have to be via VBA.
    (Location Australia, then UK, but now USA. Heart, outlook, attitude, etc always Australian)
    Quote: "All Happiness is the release of internal pressure"

  8. #8
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Change Format (Excel XL)

    Cell number formatting is based on cell contents not the contents of another cell.

    Conditional formatting does not affect the cell number format in any way.

    Steve

  9. #9
    5 Star Lounger Ruff_Hi's Avatar
    Join Date
    Feb 2001
    Location
    New York, New York, USA
    Posts
    768
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Change Format (Excel XL)

    Ok, thx Steve
    (Location Australia, then UK, but now USA. Heart, outlook, attitude, etc always Australian)
    Quote: "All Happiness is the release of internal pressure"

  10. #10
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Change Format (Excel XL)

    A formula option is to leave D alone and display as desired in Col E (though COl E would be text). You could all the calcs with D, but use E in printouts/display:
    E1:
    =IF(ISBLANK(D1),"",IF($B$2=1,TEXT(D1,"0.0%"),IF($B $2=2,TEXT(D1,"0.000"),TEXT(D1,"General"))))

    Copy down the column as desired...

    Steve

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •