Replace with formatting in formulas

[bcmccormack]

I have a formula that needs some formatting applied to part of it but
can't figure out how to do so. Here's what I have:

=A1&CHAR(10)&B1

Basically, I'm wanting to do a search and replace in the entire
worksheet so that the last cell referenced in the formula has italics
applied to it so that the formula would be (in plain English):

=A1&CHAR(10)&Italics(B1)

First, how would I do the search and replace? Finally, how would I
apply the italics to everything after my newline character?

 

[FSt1]

hi,
Formulas return values. they cannot perform actions like change the fonts.
you can have different fonts inside a single cell profided that you have
hard data inside the cell but a formula is different. the characters in the
formula are different from the values displayed and therefore take the
formating of the cell.
what you want cannot be done with a formula
Sorry
regards
FSt1

 

[bcmccormack]

I was afraid that would be the answer. If I can't do it in the
formula, how else might I be able to achieve the desired effect?

Let's say that I copy the entire worksheet and paste as values into
another worksheet. How would I be able to do a find and replace on
all cells that contain a newline character and italicize everything
following that newline character?

 

[Gord Dibben]

First of all there is no method of formatting parts of a formula return to
Italics as you wish.

Second, to do a search for CHAR(10) can be done by entering CTRL + j in the Find
box.

That won't do you any good in this case.

You would have to convert the formula to a value then use VBA to Italicize the
portion you wish.

If you want to make an attempt at that we could get you something.

Try this for starters................

Sub Highlight_Word()
Dim rng As Range
Dim cell As Range
Set rng = Selection
For Each cell In rng
cell.Copy
cell.PasteSpecial Paste:=xlValues
v = cell.Value
For i = 1 To Len(v)
If (Mid(v, i, 1)) = Chr(10) Then
cell(1, 1).Characters(Start:=i, _
Length:=30).Font.FontStyle = "Italic"
End If
Next i
End If
Application.CutCopyMode = False
Next cell
End Sub

Note: this will leave the CHAR(10) character in the string. You might want to
get rid of it with a space or something after you run the above.


Gord Dibben MS Excel MVP