Excel VBA to underline referenced cell data

Question

I have a string of text in cell A10, which begins with data from Sheet "Input," cell C2. I want the referenced data to be underlined. For example, if "John Smith" was in Input!C2, it should look like "John Smith is having trouble with this formula" with John Smith underlined. Below is the code I have, but it's not working -- it's underlining the entire string of text. Also, while I'm here, how can I get this to run automatically, rather than having to manually run the macro? Thanks in advance.

Sub Macro()

With Range("A10")
    .Value = Range("Input!C2") & " is having trouble with this formula"
    .Characters(1, Len(Range("Input!C2"))).Font.Underline = True
End With

End Sub

Should `.Font.Underline` be `xlUnderlineStyleSingle` or `xlUnderlineStyleNone`? — PatricK, Mar 20 '18 at 23:59
Look at the helper sub key_in_phrase_helper [here](https://stackoverflow.com/questions/32860792/count-and-highlight-keywords-within-phrases/32878493#32878493). — , Mar 21 '18 at 00:06
You need to add Worksheet_Change event on *Input* worksheet and only call the Macro when target includes "C2" — PatricK, Mar 21 '18 at 00:07
@PatricK - Actually, *'which begins with data from Sheet "Input," cell C2'* suggests to me that this the result of a formula and may not be able to be done at all. — , Mar 21 '18 at 00:09
drec4s, so cell A10 will update when I change the value of Input!C2 — mike437, Mar 21 '18 at 00:10
You need a worksheet_change event sub for the Input worksheet, not the one with john smith. — , Mar 21 '18 at 00:12

PatricK · Accepted Answer · 2018-03-21T02:28:50.660

2

Try this in normal module (assuming the "Contract" is where your A10 is)...

Option Explicit

Sub Macro()
    Dim sTxt As String
    Application.EnableEvents = False ' Added this
    With Worksheets("Contract").Range("A10")
        .Font.Underline = xlUnderlineStyleNone
        sTxt = Range("Input!C2").Text
        If Len(sTxt) = 0 Then
            .ClearContents
        Else
            .Value = sTxt & " is having trouble with this formula"
            .Characters(1, Len(sTxt)).Font.Underline = xlUnderlineStyleSingle
        End If
    End With
    Application.EnableEvents = True ' Added this
End Sub

Then in Input worksheet module (updated):

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim oRng As Range
    Set oRng = Union(Range("B2"), Range("B4"), Range("C2"))
    If Not Intersect(Target, oRng) Is Nothing Then Macro
End Sub

NOTE
If the Input!C2 contains formula, you need to Union other ranges involved in the Intersect() or it won't auto update on their changes.

Alternatively, you can force auto update on the Worksheet calculation, add below to the Input worksheet module:

Private Sub Worksheet_Calculate()
    Macro ' Will not work if Worksheet calculation is Manual
End Sub

Screenshots for where the code goes:

edited Mar 21 '18 at 02:28

answered Mar 21 '18 at 00:20

PatricK

6,375
1
21
25

Thanks @PatricK. Definitely on the right track, and I would never gotten this close without your help. I'm running into an error with the Worksheet_Calculate. First, to address a couple things, Sheet1 is Contract, and C2 is a formula, which is =IF(B4>0,CONCATENATE(B2," & ",B4),B2) – mike437 Mar 21 '18 at 01:40
What error you have for `Worksheet_Calculate` event? See updated code for C2's formula. – PatricK Mar 21 '18 at 01:51
Well I had the first set of code in ThisWorkbook instead of a module, so I was receiving a "Sub or function not defined" error. Then I moved it to a module, and it killed my workbook. Excel has completely locked up and I can't seem to recover it yet. – mike437 Mar 21 '18 at 02:01
I just had to disable macros, save as a regular workbook, then save again as a macro enabled workbook to strip that code, because the workbook wouldn't load. – mike437 Mar 21 '18 at 02:06
Make sure you place codes into correct location... screenshots added, Module1 code updated. Try comment out the `Worksheet_Calculate` first. – PatricK Mar 21 '18 at 02:29
It looks like it's working, but when I change the values in the Input worksheet, the debugger pops up and highlights the "Private Sub Worksheet_Change(ByVal Target As Range)" in the Input sheet. – mike437 Mar 21 '18 at 02:34
Actually I just caught your edit about commenting out the Worksheet_Calculate, and it looks like it's working perfectly – mike437 Mar 21 '18 at 02:36
Thank you for your time and help! – mike437 Mar 21 '18 at 02:39
Excellent you got it to work. Remember if `Input!B2`, `Input!B4` has formula, add those range to the union part. Or if you don't want to track this range involvement, just have `Worksheet_Change` to call `Macro` without checking which cells in Input has changed. – PatricK Mar 21 '18 at 02:39

Excel VBA to underline referenced cell data

1 Answers1

Linked