Results 1 to 5 of 5
  1. #1
    Silver Lounger
    Join Date
    Mar 2001
    Location
    Dallas, Texas, USA
    Posts
    1,680
    Thanks
    0
    Thanked 1 Time in 1 Post

    Custom Format (XL2003)

    In A6, I have the fomula:

    =CONCATENATE(A1," ", A2," - ",TEXT(A3,"mm-dd-yy"))

    Is there additional formatting I can add to TEXT(A3,"mm-dd-yy")) to make that portion of the result bold? Even though the contents of cell A3 are already bold, that is not recognized or used in the formula result. The result of the formula should like like this: Cleveland Ohio - 04-27-09

    Adding the bold attribute to the entire cell A6 obviously doesn't do what's needed. I've googled on Excel Custom Formatting and there's plenty of results for adding <font color=blue>c</font color=blue><font color=red>o</font color=red><font color=448800>l</font color=448800><font color=blue>o</font color=blue><font color=red>r</font color=red>, but not bold or italics.

    Thanks,
    - Ricky

  2. #2
    5 Star Lounger
    Join Date
    Aug 2001
    Location
    Surrey, United Kingdom
    Posts
    1,001
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Custom Format (XL2003)

    Hi Tricky

    I was given this snippet of code to make my first word red and bold some time ago, I don't know if you can adapt it for your use

    Sub JoinCellsWithRedBoldFormatForFirstWord()
    On Error Resume Next
    ' exit if not range
    If TypeName(Selection) <> "Range" Then Exit Sub
    Dim c As Range
    For Each c In Selection
    ' ignore cells in Columns A & B
    If c.Column = 1 Or c.Column = 2 Then GoTo StartOver
    ' ignore cells with no values
    If Cells(c.Row, 1) = "" Or Cells(c.Row, 2) = "" Then GoTo StartOver
    ' cell value is Columns A & B with a space between
    c = Cells(c.Row, 1) & " " & Cells(c.Row, 2)
    ' revert formatting just in case
    With c.Font
    .ColorIndex = 0
    .Bold = False
    End With
    ' add bold / red font for values in Column A
    With c.Characters(Start:=1, Length:=Len(Cells(c.Row, 1))).Font
    .FontStyle = "Bold"
    .ColorIndex = 3
    End With
    StartOver:
    Next
    End Sub

    Cheers

    Steve
    Cheers

    Steve

    Asking the questions everbody wants the answers too but feels too stupid to ask themselves :-)

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

    Re: Custom Format (XL2003)

    SteveHocking's code will not work with formulas. It is *not* possible to format part of a formula result.
    If you want to format the date differently, keep it in a different cell. You could, for example, enter the formula =A1&" "&A2&" - " in A6 and right-align it, and enter =A3 in B6, format it as mm-dd-yy and left-align it.

  4. #4
    5 Star Lounger
    Join Date
    Aug 2001
    Location
    Surrey, United Kingdom
    Posts
    1,001
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Custom Format (XL2003)

    Hi Tricky

    If you want to go down the code route you could try this code

    Sub BoldPartText()
    Dim Part1Len, Part2Len, DividerLen As Integer
    Dim Divider As String
    Part1Len = Len(Range("A1")) + 1
    Part2Len = Len(Range("A2"))
    Divider = "- "
    DividerLen = Len(Divider)

    Range("A3") = Range("A1") & Divider & Range("A2")
    With Range("A3").Characters(Start:=Part1Len + DividerLen, Length:=Part2Len).Font
    .FontStyle = "Bold"
    End With
    End Sub

    I tried it and added a button and on clicking it bolded the part after the concatenate formula after the - .

    I tried to adapt it so that on clicking the button it would bold every third cell, ie. A3, A6, A9 etc. it might be easier if the concatenate was A1, B1 result in C1 and then have a button to bold the reults in Column C but it would take a mind immeasurably superior to mine to do it.

    I found this answer on another forum here Bold part of cell

    HTH

    Cheers

    Steve
    Cheers

    Steve

    Asking the questions everbody wants the answers too but feels too stupid to ask themselves :-)

  5. #5
    Silver Lounger
    Join Date
    Mar 2001
    Location
    Dallas, Texas, USA
    Posts
    1,680
    Thanks
    0
    Thanked 1 Time in 1 Post
    Steve / Hans,

    Thanks for the responses. I really wanted to solve the issue using formatting instead of code, but I half-way knew it wasn't going to be possible (which Hans confirmed). Sometimes, Excel will surprise with an "undocumented feature".

    SteveH, I've copied the code you provided and filed away for future use - I appreciate it.

    Ricky
    - Ricky

Posting Permissions

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