Got it, let's tackle this problem step by step. We'll first fix the macro to only underline the Input!C2 portion of the text in A10, then set up the macro to run automatically when needed.

Fixing the Underline Formatting #

Your original macro was applying underline to the entire cell because it wasn't targeting just the length of the Input!C2 content. Here's a revised macro that only underlines the referenced text:

Sub FormatA10Underline()
    Dim inputText As String
    Dim fullText As String
    Dim inputLength As Integer
    Dim targetSheet As Worksheet
    
    ' Replace "YourSheetName" with the actual name of the sheet containing cell A10
    Set targetSheet = ThisWorkbook.Worksheets("YourSheetName")
    
    ' Get the content from Input!C2
    inputText = ThisWorkbook.Worksheets("Input").Range("C2").Value
    
    ' Build the full text string
    fullText = inputText & " is having trouble with this formula"
    
    ' Write the full text to A10
    targetSheet.Range("A10").Value = fullText
    
    ' Calculate the length of the input text
    inputLength = Len(inputText)
    
    ' Only apply underline to the inputText portion if it's not empty
    If inputLength > 0 Then
        ' Underline the first part (Input!C2 content)
        With targetSheet.Range("A10").Characters(Start:=1, Length:=inputLength).Font
            .Underline = xlUnderlineStyleSingle
        End With
        ' Ensure the rest of the text has no underline
        With targetSheet.Range("A10").Characters(Start:=inputLength + 1, Length:=Len(fullText) - inputLength).Font
            .Underline = xlUnderlineStyleNone
        End With
    End If
End Sub

Make sure to replace YourSheetName with the actual name of the worksheet where cell A10 is located. This macro first writes the full text to A10, then targets only the characters matching the length of Input!C2 to apply the underline, leaving the rest of the text unformatted.

Setting Up Automatic Execution #

To make this macro run without manual input, we can use Excel's worksheet events. The most logical trigger is when Input!C2 is modified, since that's the value driving the formatted text. Here's how to set it up:

1. Open the VBA Editor by pressing Alt + F11.
2. In the left Project Explorer pane, find and double-click the Input worksheet.
3. In the code window that opens, use the dropdown menus at the top:
   • Select Worksheet from the first dropdown.
   • Select Change from the second dropdown.
4. Paste this code into the generated event procedure:

Private Sub Worksheet_Change(ByVal Target As Range)
    ' Check if the modified cell is C2
    If Not Intersect(Target, Me.Range("C2")) Is Nothing Then
        ' Run the formatting macro
        FormatA10Underline
    End If
End Sub

Now, whenever you edit the content of Input!C2, the macro will automatically update cell A10 with the correctly underlined text.

If you also want the macro to run when you first open the workbook, you can add this to the ThisWorkbook module:

1. Double-click ThisWorkbook in the Project Explorer.
2. Select Workbook from the first dropdown, then Open from the second.
3. Paste this code:

Private Sub Workbook_Open()
    FormatA10Underline
End Sub

This ensures A10 is properly formatted right when you open the file.

内容的提问来源于stack exchange，提问作者mike437