Formatting Part of Text String a Different Size

[briancrosier]

I am currently tring to format a form to look how I would like it to. I have
run into a problem. the current cell has the has a formula

=""Date Signed[Alt+Enter]"&TEXT(A1,"MM/DD/YY")

I currently have the cell font at an 8, which is what I would like the Date
Signed text to show, however I would like the date that I bring in to be at a
12 font on the next line. Can I set the format in the text command to resize
the text, or is there another way to do this?

 

[Dave Peterson]

Formulas don't support this kind of formatting.

=""Date Signed[Alt+Enter]"&TEXT(A1,"MM/DD/YY")
could be:
="Date Signed"&char(10)&TEXT(A1,"MM/DD/YY")

You could use two different cells or you could convert to a value and then use
this kind of formatting.

I am currently tring to format a form to look how I would like it to. I have
run into a problem. the current cell has the has a formula

=""Date Signed[Alt+Enter]"&TEXT(A1,"MM/DD/YY")

I currently have the cell font at an 8, which is what I would like the Date
Signed text to show, however I would like the date that I bring in to be at a
12 font on the next line. Can I set the format in the text command to resize
the text, or is there another way to do this?

 

[Rick Rothstein]

As Dave mentioned, formulas can't have parts of their evaluated text
formatted differently than other parts of the text... it's all or nothing.
However, if a VB solution is acceptable, we can use event code to replace
your formula and it work exactly as you want it to. Delete your formula from
the cell or cells that it currently is in. Next, right-click the tab at the
bottom of your worksheet and select View Code from the popup menu that
appears. Then, copy/paste this code into the code window that appeared...

Private Sub Worksheet_Change(ByVal Target As Range)
Const TargetAddresses As String = "A1:A10"
Const ColumnOffsetFromTarget As Long = 1
Const RowOffsetFromTarget As Long = 0
Dim T As Range, R As Range
Set T = Intersect(Target, Range(TargetAddresses))
If Not T Is Nothing Then
For Each R In T
With R.Offset(RowOffsetFromTarget, ColumnOffsetFromTarget)
.Clear
If IsDate(R.Value) Then
.Value = "Date Signed" & vbLf & Format(R.Value, "mm/dd/yy")
.Characters(1, 11).Font.Size = 8
.Characters(13).Font.Size = 12
End If
End With
Next
End If
End Sub

Change the assigned values in the three Const statements located at the
beginning of the code to reflect your actual conditions. The TargetAddresses
is the address or addresses for the cell or cells that will contain the date
entries. The ColumnOffsetFromTarget is the number of columns offset to the
cell where the "Date Signed..." text is to go. The RowOffsetFromTarget is
the number of rows offset to the cell where the "Date Signed..." text is to
go. So, for example, is the valid range of cells that can get a date entry
is A1:A10 and you put a date in A3, then using the example numbers I set in
the Const statements, its "Date Signed..." text would be placed in B3