Underline Text in a Formula

M

MikeGeno

Board Regular
Joined
Apr 4, 2008
Messages
130
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?
 
Last edited:

Excel Facts

Highlight Duplicates
Home, Conditional Formatting, Highlight Cells, Duplicate records, OK to add pink formatting to any duplicates in selected range.
Sort by date Sort by votes
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.
 
Upvote 0
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
 
Upvote 0
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:

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.
 
Upvote 0
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.
 
Last edited:
Upvote 0
Mike

Why not use Word mail merge?:)
 
Upvote 0
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.
Click to expand...

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:

Code: 
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.
 
Upvote 0
pgc01 said:
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:

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.
Click to expand...
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...."
 
Upvote 0
You must log in or register to reply here.

Similar threads

A
  • Question
Using underlined text as a condition in IF formula
Replies
4
Views
950
RoryA
RoryA
D
  • Question
Very weird print margin problem, need help !
Replies
0
Views
136
dicklim39
D
V
  • Question
VBA Command to create new worksheets with dates as worksheets name based on certain conditions
Replies
1
Views
144
Volrin
V
U
  • Question
Type Mismatch Error
Replies
9
Views
190
Alex Blakenburg
A
M
  • Question
Filter Formula Pulls in Blank Cells From Range Of Data Formatted as dates
Replies
3
Views
300
Alex Blakenburg
A

Forum statistics

Threads
1,214,932
Messages
6,122,323
Members
449,077
Latest member
jmsotelo

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top