Formatting Parts of Formulas

[Guest]

I've created a formula that includes text and also variables from other
cells. Is it possible to format different parts of the formula differently,
e.g. bold some of it? For example:

="Beginning of sentence "&TEXT($D$26,"$##,####.00")

I would like to be able to make the data brought in from D26 bold.

 

[Pete_UK]

You could only do this using VBA, not using a standard worksheet
function.

Hope this helps.

Pete

 

[Guest]

I suspected as much. Unfortunately, I have no knowledge of VBA. Looks like an
opportunity to learn!

Thanks for your reply.

 

[Ron Rosenfeld]

I've created a formula that includes text and also variables from other
cells. Is it possible to format different parts of the formula differently,
e.g. bold some of it? For example:

="Beginning of sentence "&TEXT($D$26,"$##,####.00")

I would like to be able to make the data brought in from D26 bold.

It is not possible if there is a formula in the cell.

It is possible if the cell is only a text string.

You can use VBA to either change the cell to it's equivalent text string, or
copy it elsewhere; and then, within the VBA sub, change the format of the
selected characters.

In your instance, something like:

========================================
Option Explicit

Sub BoldPart()
Dim rDest As Range
Dim sStart As String
Dim sD26 As Variant
Dim sEnd As String

Set rDest = Range("A1")
sStart = "Beginning of sentence "
sD26 = Range("D26").Value
sEnd = Format(sD26, "$##,####.00")

Application.ScreenUpdating = False

rDest.Value = sStart & sEnd
rDest.Characters(Len(sStart) + 1, Len(sEnd)).Font.Bold = True

Application.ScreenUpdating = True

End Sub
================================

There's a lot of redundancy for clarity. Clearly you'll have to modify it to
suit your requirements.

One thing you might want to add is error checking that the contents of D26 are
numeric.


--ron

 

[Guest]

OK. Thanks for your help!

It is not possible if there is a formula in the cell.

It is possible if the cell is only a text string.

You can use VBA to either change the cell to it's equivalent text string, or
copy it elsewhere; and then, within the VBA sub, change the format of the
selected characters.

In your instance, something like:

========================================
Option Explicit

Sub BoldPart()
Dim rDest As Range
Dim sStart As String
Dim sD26 As Variant
Dim sEnd As String

Set rDest = Range("A1")
sStart = "Beginning of sentence "
sD26 = Range("D26").Value
sEnd = Format(sD26, "$##,####.00")

Application.ScreenUpdating = False

rDest.Value = sStart & sEnd
rDest.Characters(Len(sStart) + 1, Len(sEnd)).Font.Bold = True

Application.ScreenUpdating = True

End Sub
================================

There's a lot of redundancy for clarity. Clearly you'll have to modify it to
suit your requirements.

One thing you might want to add is error checking that the contents of D26 are
numeric.


--ron