Bolding specific text in a formula
Scot Rundell`
result of this, i need the text that is in cell b2, to be
bold, so that i can get an answer of 'This is great'
where 'is' is in bold.
Can anyone help?
Anon
news:050401c37b3b$548b0630$3501...@phx.gbl...
You cannot do this. Formulas create cell content, not format. Even
conditional formatting formulas can only determine whole cell formatting,
not the formatting of part of the cell contents.
GB
"Anon" <none> wrote in message news:eiPp9H2e...@TK2MSFTNGP11.phx.gbl...
Don't think this works for formulae, though
GB
"Scot Rundell`" <scot.r...@hagermeyerau.com> wrote in message
news:050401c37b3b$548b0630$3501...@phx.gbl...
Could you avoid concatenating - just put three narrow columns close
together, then apply conditional formatting to middle column
Harlan Grove
You can't do this directly. You *can* simulate this with VBA, supporting cells
and picture links. Warning: this is KLUNKY!
If you concatenate these cells in a cell in a one-column range named Mirror,
adding HTML-like formatting tags, so your formula would look like
=A1&"<B>"&B1&"</B>"&C1
Then you could use an event handler like the following very simplified one to
render these into formatted text constants in the cells to the right of Mirror.
Note: this even handler would need to be in the VBA class module for the
worksheet containing the Mirror range.
Private Sub Worksheet_Calculate()
Dim c As Range, f As String, p As Long, q As Long
On Error GoTo CleanUp
Application.EnableEvents = False
For Each c In ThisWorkbook.Names("Mirror").RefersToRange
f = c.Text
p = InStr(1, f, "<B>", vbTextCompare)
q = InStr(p, f, "</B>", vbTextCompare)
If p > 0 And q > p Then
f = Left(f, p - 1) & Mid(f, p + 3, q - p - 3) & Mid(f, q + 4)
c.Offset(0, 1).Formula = f
c.Offset(0, 1).Characters(p, q - p - 3).Font.Bold = True
End If
Next c
CleanUp:
Application.EnableEvents = True
End Sub
Finally, select the cell immediately to the right of the one containing the
concatenation formula in Mirror, Edit > Copy, then select the cell where you
want the formatted result to appear, hold down a [Shift] key, and issue the menu
command Edit > Paste Picture Link. You now have a picture link to the formatted
text that will update whenever any of the three source cells changes.
The event handler above could be made to handle much more complicated HTML-like
formatting. This is a stripped down example that's only intended to allow you to
assess whether or not this approach would work for you.
I'll repeat the warning, though: this is KLUNKY!
--
Never attach files.
Snip unnecessary quoted text.
Never multipost (though crossposting is usually OK).
Don't change subject lines because it corrupts Google newsgroup archives.