Excel单元格部分内容加粗失效求助：更新后全单元格内容变粗

Hey there, let's tackle this issue you're facing—where partial cell bold formatting works the first time, but on subsequent updates the entire cell goes bold. I've seen this exact problem before, and it almost always boils down to not clearing existing formatting before reapplying changes, plus accidentally triggering recursive events in the Worksheet_Change handler.

The Root Cause #

When you update the cell the second time, the old partial bold formatting is still present. If your code doesn't reset the cell's font first, you might end up applying bold to the entire cell without realizing it. Also, modifying the cell's value inside the Worksheet_Change event will trigger the event again, leading to unexpected formatting overlaps.

Fixed Code Solution #

Here's a revised version of your code that addresses both issues. I'll assume you're working with columns I and J (matching your mentioned case 9 and 10), and that you want to bold a specific segment like a comment string:

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim comment As String
    Dim tim As String
    Dim fullContent As String
    Dim boldStartPos As Integer
    Dim boldTextLength As Integer
    
    ' Only target columns I/J and single-cell changes
    If Not Intersect(Target, Me.Range("I:J")) Is Nothing And Target.Cells.Count = 1 Then
        ' Disable events to avoid recursive triggering when modifying the cell
        Application.EnableEvents = False
        
        ' Error handling to ensure events get re-enabled even if something breaks
        On Error GoTo Cleanup
        
        ' Step 1: Reset entire cell font to non-bold first
        Target.Font.Bold = False
        
        ' Replace this with your actual logic to build tim and comment
        tim = "Updated at: " & Format(Now(), "hh:mm:ss")
        comment = "Priority adjustment logged"
        
        ' Combine text segments
        fullContent = tim & " | " & comment
        
        ' Calculate position/length of the text to bold
        boldStartPos = Len(tim) + 3 ' +3 accounts for the " | " separator
        boldTextLength = Len(comment)
        
        ' Write full content to the cell
        Target.Value = fullContent
        
        ' Apply bold ONLY to the target segment
        If boldTextLength > 0 Then
            Target.Characters(Start:=boldStartPos, Length:=boldTextLength).Font.Bold = True
        End If
    End If

Cleanup:
    ' Re-enable events no matter what
    Application.EnableEvents = True
    ' Optional: Show error message for debugging
    If Err.Number <> 0 Then
        MsgBox "Error: " & Err.Description, vbCritical
        Err.Clear
    End If
End Sub

Key Improvements #

• Event Disabling: Application.EnableEvents = False stops the Worksheet_Change event from firing again when we update the cell's value, preventing unintended loops.
• Format Reset: Target.Font.Bold = False clears any existing bold formatting before applying new changes, so old bold text doesn't stick around and merge with new content.
• Precise Bold Targeting: Using Target.Characters(Start:=..., Length:=...) ensures we only bold the exact segment you want, instead of accidentally affecting the entire cell.

Just adjust the logic where you build tim and comment to match your actual code, then replace your existing case 9/10 formatting code with this approach. You'll find partial bold works consistently even after multiple updates.

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