Page 1 of 2 12 LastLast
Results 1 to 15 of 18
  1. #1
    3 Star Lounger Omega3's Avatar
    Join Date
    Jan 2004
    Location
    Los Angeles, California, USA
    Posts
    343
    Thanks
    2
    Thanked 1 Time in 1 Post

    Formatting (2002)

    I have a formula in a cell that uses a named range like this:

    ="Error with vendor # - " & VendorCode

    Is there a way to make the data represented by the VendorCode named range to be bold faced so that it stands out?
    You know it's time to diet when you push away from the table and the table moves.

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

    Re: Formatting (2002)

    No, that is not possible, you can only format individual characters in constant values, not in the result of a formula.
    You could use 2 neighboring cells, one containing the text Error with vendor # - and one with the formula =VendorCode. You can then format the second cell as bold.

  3. #3
    3 Star Lounger Omega3's Avatar
    Join Date
    Jan 2004
    Location
    Los Angeles, California, USA
    Posts
    343
    Thanks
    2
    Thanked 1 Time in 1 Post

    Re: Formatting (2002)

    Thanks for the quick response. I can't think of a way for VBA to help me either. Putting it in two cells would work but the alignment with the rest of the spreadsheet would be messed up. Seems like I will have to do without it. Thanks again.
    You know it's time to diet when you push away from the table and the table moves.

  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: Formatting (2002)

    If you are going to use VB, an option would be to convert the formula to a value, and then have VB convert the appropriate chars to bold.

    Your macro would have to update the cell as appropriate with changes to the value since you would no longer have a formula.

    Steve

  5. #5
    3 Star Lounger Omega3's Avatar
    Join Date
    Jan 2004
    Location
    Los Angeles, California, USA
    Posts
    343
    Thanks
    2
    Thanked 1 Time in 1 Post

    Re: Formatting (2002)

    Thanks Steve. I could do a copy and paste values only into another cell. How would I go about converting certain characters I want to bold face?
    You know it's time to diet when you push away from the table and the table moves.

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

    Re: Formatting (2002)

    Not directly. Depending on whether the cell represented by the name VendorCode contains a constant or a formula, you could use the worksheet change event routine or the worksheet calculate routine to build the string as a constant, put it in the target cell, and format it however you want.

    The attached worksheet shows how it would work with a constant in A1 and a formula in A2 and the results in D1 and D2 respectivly.
    Legare Coleman

  7. #7
    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: Formatting (2002)

    Here is an example. You can add this to the workbook object in VB of the appropriate sheet (not a regular module):
    <pre>Option Explicit
    Private Sub Worksheet_Change(ByVal Target As Excel.Range)
    Dim str1 As String
    Dim str2 As String
    If Not Intersect(Target, Range("VendorCode")) Is Nothing Then
    str1 = "Error with Vendor # - "
    str2 = Range("VendorCode").Value
    Application.EnableEvents = False
    With Range("a1")
    .Value = str1 & str2
    .Characters(Start:=Len(str1) + 1, Length:=Len(str2)). _
    Font.FontStyle = "Bold"
    End With
    Application.EnableEvents = True
    End If
    End Sub</pre>


    When vendor code is changed, the code runs and changes the contents of cell A1 (change as desired)

    Steve

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

    Re: Formatting (2002)

    I see that Steve has replied with code that is similar to what is in the workbook in my reply. However, I think that what you were asking in this question was how to format the vendor code as bold manually after you Paste Special the string into a cell. You would do this by selecting the cell, and then select the text that you want to format in the formula bar. Then either click on the formatting you want in the toolbar, or select Cells from the Format menu and select the formatting you want.
    Legare Coleman

  9. #9
    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: Formatting (2002)

    <img src=/S/blush.gif border=0 alt=blush width=15 height=15> I didn't see your post or I would not have written the code...

    Steve

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

    Re: Formatting (2002)

    No problem there, I was more concerned that it looked like you misunderstood what the poster was asking. Better to have too much code than too little. <img src=/S/grin.gif border=0 alt=grin width=15 height=15>
    Legare Coleman

  11. #11
    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: Formatting (2002)

    I might have misunderstood. I don't know if he had a manual way in mind or via code. I was leaning towards code since he wanted it in a formula.

    Steve

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

    Re: Formatting (2002)

    Since he was talking about copying the cell with the formula and doing a Paste Special/Values, I thought he was talking about doing it manually.
    Legare Coleman

  13. #13
    3 Star Lounger Omega3's Avatar
    Join Date
    Jan 2004
    Location
    Los Angeles, California, USA
    Posts
    343
    Thanks
    2
    Thanked 1 Time in 1 Post

    Re: Formatting (2002)

    Hi Steve, I was able to get back to this and apply your example. That character property start and length parameters really helped and it's working great now. Thanks for your time and help!
    You know it's time to diet when you push away from the table and the table moves.

  14. #14
    3 Star Lounger Omega3's Avatar
    Join Date
    Jan 2004
    Location
    Los Angeles, California, USA
    Posts
    343
    Thanks
    2
    Thanked 1 Time in 1 Post

    Re: Formatting (2002)

    Sorry for the delay. Got pulled off onto something else. I was thinking of copying and pasting values via VBA code and applying the format. Steve understood what I was trying to do and his example worked out real well. Thanks for your input too!
    You know it's time to diet when you push away from the table and the table moves.

  15. #15
    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: Formatting (2002)

    <img src=/S/whisper.gif border=0 alt=whisper width=29 height=17>Legare also understood. His example code (with a spreadsheet) is almost the same as mine and was posted first. His manual way was a followup answer thinking you were doing it manually.

    Steve

Page 1 of 2 12 LastLast

Posting Permissions

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