Bold items within text string

[Steve]

Hi All,

I found a bit of code on the newsgroup that bolds the text within a
cell during a concatenation of several cells:
Sub BoldNotesTitle()

Dim s As String, sA1 As String, sA2 As String
Const s1 As String = "On "
Const s2 As String = ", please "
Const s3 As String = " funds for a margin call."


sA1 = Range("A1").Text
sA2 = Range("A2").Text


With Range("A4")
.Value = s1 & sA1 & s2 & sA2 & s3
.Characters(1 + Len(s1), Len(sA1)).Font.Bold = True
.Characters(1 + Len(s1) + Len(sA1) + Len(s2), Len(sA2)).Font.Bold
= True
End With

End Sub

I'm trying to take this concept and apply it to a range. I wrote a
formula (below) that does the concatenation I need. Somehow I need to
take my formula into code and BOLD the cells J$1, K$1, L$1, M$1, N$1.
My biggest question is how to I apply this formula to the range
H2:H500? Thanks!

=IF(ISBLANK(J2),"",J$1&" - "&J2&CHAR(10))&IF(ISBLANK(K2),"",K$1&" -
"&K2&CHAR(10))&IF(ISBLANK(L2),"",L$1&" -
"&L2&CHAR(10))&IF(ISBLANK(M2),"",M$1&" -
"&M2&CHAR(10))&IF(ISBLANK(N2),"",N$1&" - "&N2&CHAR(10))

 

[Rick Rothstein]

Somehow I need to take my formula into code

and BOLD the cells J$1, K$1, L$1, M$1, N$1.

You cannot bold parts of text output by a formula... its either bold all the
displayed text or not. You can only bold parts of the text in a cell if that
text is a text constant.

Rick Rothstein (MVP - Excel)

 

[Steve]

Thanks Rick. How about this - since the parts of the text string I
want bolded are constant (ie, column headers J$1:N$1), can I read the
column headers in variables, and do a FIND routine to find each column
header in the text string and then bold?

 

[Rick Rothstein]

Thanks Rick. How about this - since the parts of the text string I

want bolded are constant (ie, column headers J$1:N$1), can I read
the column headers in variables, and do a FIND routine to find
each column header in the text string and then bold?

You cannot bold individual parts or text generated by a formula... there is
no way around that using formulas. If you were willing to turn the
calculating of the formula to VB in a Change event procedure, then you could
have that procedure inject pure text into the cell and that text, being a
constant, could be bolded in parts.

Rick Rothstein (MVP - Excel)

 

[Steve]

Thanks Rick. I'm certainly willing, but have no idea how to do that!

 

[Steve]

Rick,

I created a formula in a cell to do what I wanted, then hard-coded the
entire column. Now I have a column of values, with no formulas. I
tried to do a simple Replace (ie find Bob and replace with Bob in
bold), but found that when it finds the text string I'm trying to
replace, it adjusts all contents of the cell.

 

[Gord Dibben]

Your example is quite simple and I imagine you need more but here's a start.

Select your range of hard-coded cells and run this macro to bold Bob in all
cells.

Sub Bold_String()
Dim rng As Range
Dim cell As Range
Dim start_str As Integer
Set rng = Selection
For Each cell In rng
start_str = InStr(cell.Value, "Bob")
If start_str Then
cell.Characters(start_str, 3).Font.Bold = True
End If
Next
End Sub


Gord Dibben MS Excel MVP

 

[Steve]

Thanks Gord. Yes, reality is a bot more complicated than "Bob"! But
this is a one-time routine, so no need to get more sophisticated with
code. Thanks for your help!