none
how to bold some words inside a cell RRS feed

  • Question

  • Hello,

    I'm making a list of names inside a single cell. The idea is to bold the cells when the name met some conditions.

    At the moment I'm able to bold only the last word. When the condition is true in another cycle, it turn off the bold, and bold the new name.

    Because the code I'm running is too large to post it here (and the condition is met when more than one cell in more than one sheet, so it will be hard to understand. Also my comments are in spanish, so...), I made a short version of it, where is only this piece that is making trouble.

    To test it, you only have to write a new name in B1 and a last name in B2. If the name starts with an "A", then it should bold that name and last name. Else not.

    I'll be very appreciate if someone could reach in to a solution to bold every single time the condition is true.

    Thanks!!

    Sub BoldTest()
    Dim strName As String, strLastName As String
    Dim strOldText As String, strNewText As String
    Dim intLenOldText  As Integer, intLenNewText As Integer

    strName = Cells(1, 2)
    strLastName = Cells(2, 2)

    strOldText = Cells(1, 1)
    strNewText = strName & " " & strLastName

    intLenOldText = Len(Cells(1, 1)) + 3
    intLenNewText = Len(strNewText)

    If Left(strName, 1) = "A" Then
    Cells(1, 1) = strOldText & ", " & strNewText
    Cells(1, 1).Characters(intLenOldText, intLenNewText).Font.Bold = True
    Else
    Cells(1, 1) = strOldText & ", " & strName & " " & strLastName
    End If

    End Sub

    Friday, August 28, 2015 3:23 AM

Answers

  • Hi RaimundoO,

    >> At the moment I'm able to bold only the last word. When the condition is true in another cycle, it turn off the bold, and bold the new name.

    In my option, it was caused by this code below, when you add new text to cell(1,1), it will clear the font style of the old text in cell(1,1,), and format the new text with the last text with bold.

    Cells(1, 1) = strOldText & ", " & strNewText
    Cells(1, 1).Characters(intLenOldText, intLenNewText).Font.Bold = True

    Thank Hans for the code. I think his code will help you forbid the text bold which do not meet your condition. To keep the format of the original text, and format the new insert text, I suggest you try the code below:

    Sub BoldTest()
     Dim strName As String, strLastName As String
     Dim strOldText As String, strNewText As String
     Dim intLenOldText  As Integer, intLenNewText As Integer
    
     strName = Cells(1, 2)
     strLastName = Cells(2, 2)
    
     strOldText = Cells(1, 1)
     strNewText = strName & " " & strLastName
    
     intLenOldText = Len(Cells(1, 1)) + 3
     intLenNewText = Len(strNewText)
    
    If Left(strName, 1) = "A" Then
    Cells(1, 1).Characters(intLenOldText - 2).Insert ", " & strNewText
    Cells(1, 1).Characters(intLenOldText, intLenNewText).Font.Bold = True
    Else
    'Cells(1, 1) = strOldText & ", " & strName & " " & strLastName
    
    Cells(1, 1).Characters(intLenOldText - 2).Insert ", " & strNewText
    Cells(1, 1).Characters(intLenOldText - 2, intLenNewText + 2).Font.Bold = False
    End If
    End Sub
    

    Best Regards,

    Edward


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.


    Monday, August 31, 2015 2:25 AM

All replies

  • Replace the line

    Cells(1, 1) = strOldText & ", " & strName & " " & strLastName

    with

            Cells(1, 1).Characters(intLenOldText - 2).Insert ", " & strNewText
            Cells(1, 1).Characters(intLenOldText - 2, intLenNewText + 2).Font.Bold = False


    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    • Proposed as answer by André Santo Friday, August 28, 2015 11:55 AM
    Friday, August 28, 2015 9:41 AM
  • Hi RaimundoO,

    >> At the moment I'm able to bold only the last word. When the condition is true in another cycle, it turn off the bold, and bold the new name.

    In my option, it was caused by this code below, when you add new text to cell(1,1), it will clear the font style of the old text in cell(1,1,), and format the new text with the last text with bold.

    Cells(1, 1) = strOldText & ", " & strNewText
    Cells(1, 1).Characters(intLenOldText, intLenNewText).Font.Bold = True

    Thank Hans for the code. I think his code will help you forbid the text bold which do not meet your condition. To keep the format of the original text, and format the new insert text, I suggest you try the code below:

    Sub BoldTest()
     Dim strName As String, strLastName As String
     Dim strOldText As String, strNewText As String
     Dim intLenOldText  As Integer, intLenNewText As Integer
    
     strName = Cells(1, 2)
     strLastName = Cells(2, 2)
    
     strOldText = Cells(1, 1)
     strNewText = strName & " " & strLastName
    
     intLenOldText = Len(Cells(1, 1)) + 3
     intLenNewText = Len(strNewText)
    
    If Left(strName, 1) = "A" Then
    Cells(1, 1).Characters(intLenOldText - 2).Insert ", " & strNewText
    Cells(1, 1).Characters(intLenOldText, intLenNewText).Font.Bold = True
    Else
    'Cells(1, 1) = strOldText & ", " & strName & " " & strLastName
    
    Cells(1, 1).Characters(intLenOldText - 2).Insert ", " & strNewText
    Cells(1, 1).Characters(intLenOldText - 2, intLenNewText + 2).Font.Bold = False
    End If
    End Sub
    

    Best Regards,

    Edward


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.


    Monday, August 31, 2015 2:25 AM