Underline Text in a Formula

[MikeGeno]

I am having an issues underlining text in a formula and have run out of ideas.

I have a legal contract that we print out from excel and I have 2 words that need to be underlined.

My Formula is as like the one below

=" The Agreement between (The "Company") and"&D1&"(The "Customer")"



I need to have the Company and the Customer Underlined, but since I have an & in the formula it will not let me.

Any Ideas?

 

[pgc01]

Hi Mike

That's not possible with a formula. The whole text that results out of a formula will have the same format.

If you need the cell with rich text, one solution is to use vba and populate the cell with the worksheet change event.

 

[MikeGeno]

Thanks for the help this means I may have to break the cell up and it does not look nice as the name of customer changes length, I end up with gaps in the text.

I am not sure what rich text is or the worksheet change event, is that something difficult to put in place, still a newbie to coding

 

[pgc01]

Do this test.

- right-click on the worksheet tab and choose View Code

You'll be taken to the worksheet module in the vb editor.

- Paste this code:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim s1 As String, s2 As String
 
' only if D1 changes
If Target.Address <> "$D$1" Then Exit Sub
 
' write the text in A1
s1 = " The Agreement between (The ""Company"") and "
s2 = " (The ""Customer"")"
Range("A1") = s1 & Range("D1") & s2
 
' add underline to the format of the words "Company" and "Customer"
Range("A1").Characters(InStr(1, Range("A1"), "Company", vbTextCompare), Len("Company")).Font.Underline = True
Range("A1").Characters(InStr(1, Range("A1"), "Customer", vbTextCompare), Len("Customer")).Font.Underline = True
 
End Sub

Now go back to the worksheet. Each time you write a new value in D1, cell A1 gets the text with the words underlined.

 

[MikeGeno]

pgc thank you so much for the help I think I need one more piece of information to make this work.


Cell D1 is a vlookup formula that references a pageby field in a pivot table in cell V131.

When I change v131 the name in cell D1 changes.

How can I tweak this so that when V131 changes it pulls the name from D1?

It is not updating in the name of the customer unless I physically type over what is there.

 

[Norie]

Mike

Why not use Word mail merge?

 

[pgc01]

Cell D1 is a vlookup formula that references a pageby field in a pivot table in cell V131.

When I change v131 the name in cell D1 changes.

How can I tweak this so that when V131 changes it pulls the name from D1?

It is not updating in the name of the customer unless I physically type over what is there.

Yes, you have to change the value in D1 (not a formula) to fire the worksheet change event.

In your case, since you are changing the pivot table, you can use instead the Worksheet_PivotTableUpdate event.

Replace the previous code with:

Private Sub Worksheet_PivotTableUpdate(ByVal Target As PivotTable)
Dim s1 As String, s2 As String
 
' write the text in A1
s1 = " The Agreement between (The ""Company"") and "
s2 = " (The ""Customer"")"
Range("A1") = s1 & Range("D1") & s2
 
' add underline to the format of the words "Company" and "Customer"
Range("A1").Characters(InStr(1, Range("A1"), "Company", vbTextCompare), Len("Company")).Font.Underline = True
Range("A1").Characters(InStr(1, Range("A1"), "Customer", vbTextCompare), Len("Customer")).Font.Underline = True
End Sub

Each time you change the page field in the pivot table, this event is fired and the text in A1 is updated.

 

[MikeGeno]

This is perfect, really appreciate the help.

Thank you

 

[manu4u4ever]

Do this test.

- right-click on the worksheet tab and choose View Code

You'll be taken to the worksheet module in the vb editor.

- Paste this code:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim s1 As String, s2 As String
 
' only if D1 changes
If Target.Address <> "$D$1" Then Exit Sub
 
' write the text in A1
s1 = " The Agreement between (The ""Company"") and "
s2 = " (The ""Customer"")"
Range("A1") = s1 & Range("D1") & s2
 
' add underline to the format of the words "Company" and "Customer"
Range("A1").Characters(InStr(1, Range("A1"), "Company", vbTextCompare), Len("Company")).Font.Underline = True
Range("A1").Characters(InStr(1, Range("A1"), "Customer", vbTextCompare), Len("Customer")).Font.Underline = True
 
End Sub

Now go back to the worksheet. Each time you write a new value in D1, cell A1 gets the text with the words underlined.

Can this be tweaked to underline whatever the formula picks from the input in F4, K4 & F6 in the following formula:

="I, " & $F$4 & ", presently posted as " & $K$4 & ", at the " & $F$6 & ", do hereby declare that...."