characters within a linked cell
 

Is there a way to change the format of how individual characters appear in a
linked cell while maintaining the link?

For example my link: ='Front page'!N43
should equal: 4.6[2] where [2] equals a superscripted 2 colored blue.
(unable to dupe this here hence [2].)

Please let me know.
Thank you.

characters within a linked cell
 

XL functions only return values to their calling cells, not formats.

You could, instead, use an event macro. Put this in your target (i.e.,
non Front Page) worksheet's code module:

Private Sub Worksheet_Calculate()
Dim rSource As Range
Dim rCharFont As Font
Dim i As Long

On Error GoTo ExitSub
With Application
.ScreenUpdating = False
.EnableEvents = False
End With
Set rSource = Worksheets("Front Page").Range("N43")
With Range("A1") 'Change to suit
.NumberFormat = "@"
.Value = rSource.Text
For i = 1 To Len(.Text)
Set rCharFont = rSource.Characters(i, 1).Font
With .Characters(i, 1).Font
.Name = rCharFont.Name
.FontStyle = rCharFont.FontStyle
.Size = rCharFont.Size
.Color = rCharFont.Color
If rCharFont.Subscript Then .Subscript = True
If rCharFont.Superscript Then .Superscript = True
.Underline = rCharFont.Underline
.Shadow = rCharFont.Shadow
.OutlineFont = rCharFont.OutlineFont
.Strikethrough = rCharFont.Strikethrough
End With
Next i
End With
ExitSub:
With Application
.EnableEvents = True
.ScreenUpdating = True
End With
End Sub



In article ,
ORLANDO V wrote:

Is there a way to change the format of how individual characters appear in a
linked cell while maintaining the link?

For example my link: ='Front page'!N43
should equal: 4.6[2] where [2] equals a superscripted 2 colored blue.
(unable to dupe this here hence [2].)

Please let me know.
Thank you.

characters within a linked cell
 

Thank you.

"JE McGimpsey" wrote:

XL functions only return values to their calling cells, not formats.

You could, instead, use an event macro. Put this in your target (i.e.,
non Front Page) worksheet's code module:

Private Sub Worksheet_Calculate()
Dim rSource As Range
Dim rCharFont As Font
Dim i As Long

On Error GoTo ExitSub
With Application
.ScreenUpdating = False
.EnableEvents = False
End With
Set rSource = Worksheets("Front Page").Range("N43")
With Range("A1") 'Change to suit
.NumberFormat = "@"
.Value = rSource.Text
For i = 1 To Len(.Text)
Set rCharFont = rSource.Characters(i, 1).Font
With .Characters(i, 1).Font
.Name = rCharFont.Name
.FontStyle = rCharFont.FontStyle
.Size = rCharFont.Size
.Color = rCharFont.Color
If rCharFont.Subscript Then .Subscript = True
If rCharFont.Superscript Then .Superscript = True
.Underline = rCharFont.Underline
.Shadow = rCharFont.Shadow
.OutlineFont = rCharFont.OutlineFont
.Strikethrough = rCharFont.Strikethrough
End With
Next i
End With
ExitSub:
With Application
.EnableEvents = True
.ScreenUpdating = True
End With
End Sub



In article ,
ORLANDO V wrote:

Is there a way to change the format of how individual characters appear in a
linked cell while maintaining the link?

For example my link: ='Front page'!N43
should equal: 4.6[2] where [2] equals a superscripted 2 colored blue.
(unable to dupe this here hence [2].)

Please let me know.
Thank you.