Using Excel Programming to bold part of the text

[Roger Bedford]

I would like to create the information below where the TEXT function result is
bold and the latter text is a normal font; see below. One person in the Excel
Function Newsgroup suggested that this could be done using Excel Programming.
=TEXT(G16*100,"00")&" cents $US"

Any sugestions will be much appreciated.

Roger

 

[Myrna Larson]

The only way to make this change automatically would be to use a
Worksheet_Change event macro. The following routine searches for the 1st space
and makes everything to the left of that space bold. That said, if I type text
in the cell, like 23 cents $US, it works. But with 0.23 in G1, it doesn't work
with your formula.


Private Sub Worksheet_Change(ByVal Target As Range)
Dim Txt As String
Dim i As Long

Txt = Target.Text
i = InStr(Txt, " ")
Target.Characters(1, i - 1).Font.Bold = True

End Sub

 

[Harlan Grove]

The only way to make this change automatically would be to use a
Worksheet_Change event macro. The following routine searches for the
1st space and makes everything to the left of that space bold. That
said, if I type text in the cell, like 23 cents $US, it works. But
with 0.23 in G1, it doesn't work with your formula.

It shouldn't work with any formula.

Private Sub Worksheet_Change(ByVal Target As Range)
Dim Txt As String
Dim i As Long

Txt = Target.Text
i = InStr(Txt, " ")
Target.Characters(1, i - 1).Font.Bold = True
End Sub

(e-mail address removed) (Roger Bedford) wrote: ....

If the cell in question contains a formula, the cell doesn't contain a
Characters object that can be used to provide substring formatting. For
example, under Excel 2000, Myrna's event handler makes the ENTIRE result of
Roger's formula bold.

Doesn't the Characters object only work with substrings only when cells
contain text constants?

 

[Peter T]

Hi Roger,

As Harlan says a font in a formula cell cannot have mixed formats.

One approach would be NOT to write a formula in the cell where you want your
cents to be displayed, and let the following Event code do it for you, along
the lines of Myrna's suggestion.

Select G1 and in the Name box left of input bar type "centsIn" without
quotes. Select the cell where you want the converted cents to be displayed
and name it "centsOut".

Right click the sheet tab > View code and paste following into the module:


Private Sub Worksheet_Change(ByVal Target As Range)
Dim rIn As Range, rOut As Range, n As Long, s As String

On Error GoTo errH
Set rIn = Range("centsIn")

If Intersect(Target, rIn) Is Nothing Then Exit Sub
Set rOut = Range("centsOut")
If IsNumeric(rIn) Then
s = rIn.Value * 100
Else: s = "error"
End If
n = Len(s)
s = s & " cents US$"
Application.EnableEvents = False
With rOut
.Font.Bold = False
.Value = s
.Characters(1, n).Font.Bold = True
End With
done:
Application.EnableEvents = True
Exit Sub
errH:
s = ""
If rIn Is Nothing Then s = "centsIn"
If rOut Is Nothing Then s = "centsOut"
If s <> "" Then MsgBox s & " does not exist"
GoTo done
End Sub


The reason for naming the cells is the code should work if subsequently you
move them.

Regards,
Peter

 

[Tushar Mehta]

A non-programmatic approach would be to put the TEXT() formula in one
cell and the "cents $US" literal in the cell to the right. Right-align
the former and left-align the latter. Now, make the entire first cell
bold.

--
Regards,

Tushar Mehta
www.tushar-mehta.com
Excel, PowerPoint, and VBA add-ins, tutorials
Custom MS Office productivity solutions

 

[Roger Bedford]

Thank you all very much for your help. I have decided to abort solving
this problem as it looks almost impossible to do in a clean manner and I
am only used to Excel 5 macros so have a long way to go to get up to the
new macros.

Again, many thanks,

Roger