+ Reply to Thread
Results 1 to 3 of 3

Change text

  1. #1
    Andersos@gmail.com
    Guest

    Change text

    Is it possible to change an enitre work sheets text to all caps? It is
    not an option under font - effects....


  2. #2
    Forum Contributor
    Join Date
    03-14-2006
    Location
    Pakistan
    Posts
    1,791

    Lightbulb

    suppose your data is in sheet1, put in cell A1 of sheet2 =UPPER(sheet1!A1) and copy it across the rows and columns in which your data is in sheet1.
    this will display text in upper case (all caps).
    if you want to copy formats from sheet1 as well then do a bit more.
    in sheet2 press Ctrl+A to select the whole sheet, then press Ctrl+C to copy the selection (dont deselect the sheet), right click on the selection and select Paste Special and check the option values and click Ok

    in sheet1 press Ctrl+A (to select all sheet) then press Ctrl+C to copy the sheet, come to sheet 2 and right click on cell A1 of sheet2 and select Paste Special and check the option Formats and click Ok.

    hopefully this would solve your problem
    Quote Originally Posted by Andersos@gmail.com
    Is it possible to change an enitre work sheets text to all caps? It is
    not an option under font - effects....

  3. #3
    Gord Dibben
    Guest

    Re: Change text

    Personally I would find an all-caps worksheet to be hard to read and quite
    jarring to the senses, but..................

    You can use the worksheet function UPPER to change one cell at a time with
    formulas or a macro to change entire sheet at once with no formulas.

    Sub optUpper_Click()
    'David McRitchie, programming, 2003-03-07
    Dim rng1 As Range, rng2 As Range, bigrange As Range
    Dim Cell As Range
    Application.ScreenUpdating = False
    Application.Calculation = xlCalculationManual
    On Error Resume Next
    Set rng1 = Intersect(Selection, _
    Selection.SpecialCells(xlCellTypeConstants))
    Set rng2 = Intersect(Selection, _
    Selection.SpecialCells(xlCellTypeFormulas))
    On Error GoTo 0
    If rng1 Is Nothing Then
    Set bigrange = rng2
    ElseIf rng2 Is Nothing Then
    Set bigrange = rng1
    Else
    Set bigrange = Union(rng1, rng2)
    End If
    If bigrange Is Nothing Then
    MsgBox "All cells in range are EMPTY"
    GoTo done
    End If
    For Each Cell In bigrange
    Cell.Formula = UCase(Cell.Formula)
    Next Cell
    done:
    Application.Calculation = xlCalculationAutomatic
    Application.ScreenUpdating = True
    End Sub


    Gord Dibben MS Excel MVP

    On 15 Aug 2006 07:38:19 -0700, Andersos@gmail.com wrote:

    >Is it possible to change an enitre work sheets text to all caps? It is
    >not an option under font - effects....



+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1