Results 1 to 4 of 4
  1. #1
    Bronze Lounger
    Join Date
    Jan 2001
    Posts
    1,418
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Format Cell Based On Its Contents (2003)

    Is there a way to format a cell (say center alignment) based on the cells content (say if the cell contains the text "N/A")?
    Thanks,
    Jeff

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Format Cell Based On Its Contents (2003)

    You can use conditional formatting (in the Format menu) to change the following aspects of cell formatting depending on the cell value (or on a more complex condition):
    - Font, font size, font style, font color.
    - Border style, border color.
    - Background color, background fill pattern.
    As you see, text alignment is not among them. It would be possible to write code for the Worksheet_Change event in the worksheet code module to change the alignment based on the cell value, but I would only use that if you *really* need it.

  3. #3
    Platinum Lounger
    Join Date
    Feb 2002
    Location
    A Magic Forest in Deepest, Darkest Kent
    Posts
    5,681
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Format Cell Based On Its Contents (2003)

    Hi Jeff

    I will probably be shot for doing an on change function but using this sub will change the format as you type in the spreadsheet cell <img src=/S/grin.gif border=0 alt=grin width=15 height=15>

    <pre>Private Sub Worksheet_Change(ByVal Target As Range)
    Dim c As Range

    For Each c In Target
    If c.Value = "n/a" Then

    c.HorizontalAlignment = xlCenter

    Else

    End If
    Next c
    End Sub
    </pre>

    Jerry

  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: Format Cell Based On Its Contents (2003)

    Why not just center align everything in the column?

    If the problem is aligning decimal points in a number, you can use the question mark in the formatting. Something like:
    ?,??0.00

    to make 2 decimals and align the decimal point and still allow numbers to be "centered"...

    This will alleviate having to use code...

    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
  •