Excel内新增VB运行宏，自动为每一个单元格内的多行内容添加序号

1.先将Excel文件格式另存为（*.xlsm）格式

2.按下alt+F11键（或者进入工具-VB编辑器），选择Sheet1

3.输入代码

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim m As String
    Dim i As Long
    Dim j As Long
    Dim mm As String
    Dim lines() As String
    Dim cmt As Range
    Dim lineText As String
    
    On Error GoTo ExitHandler
    Application.EnableEvents = False
    
    ' ======================
    ' 范围控制示例：
    ' 单行生效   If Intersect(Target, Me.Rows(5)) Is Nothing Then GoTo ExitHandler
    ' 单列生效   If Intersect(Target, Me.Columns("B")) Is Nothing Then GoTo ExitHandler
    ' 多行生效   If Intersect(Target, Me.Range("5:10")) Is Nothing Then GoTo ExitHandler
    ' 多列生效   If Intersect(Target, Me.Range("I:J")) Is Nothing Then GoTo ExitHandler
    ' ======================
    
    ' 实际启用：I-J 列生效
    If Intersect(Target, Me.Range("I:J")) Is Nothing Then GoTo ExitHandler
    
    ' ======================
    ' 序号处理逻辑
    ' ======================
    For Each cmt In Target
        If cmt.Value <> "" Then
            m = cmt.Value
            lines = Split(m, Chr(10))
            mm = ""
            j = 0
            
            For i = LBound(lines) To UBound(lines)
                lineText = Trim(lines(i))
                
                ' 如果开头已经是数字+点+空格，则去掉旧编号
                If lineText Like "#.*" Then
                    lineText = Trim(Mid(lineText, InStr(lineText, ".") + 1))
                End If
                
                If lineText <> "" Then
                    j = j + 1
                    mm = mm & j & ". " & lineText & Chr(10)
                End If
            Next i
            
            ' 去掉末尾多余换行符
            cmt.Value = Left(mm, Len(mm) - 1)
        End If
    Next cmt
    
ExitHandler:
    Application.EnableEvents = True
End Sub

如果想控制某一行或者某一列生效这个规则可以修改代码内的此部分（本代码实际生效I-J列，我只需要用到这两列）

' 范围控制示例：
' 单行生效 If Intersect(Target, Me.Rows(5)) Is Nothing Then GoTo ExitHandler
' 单列生效 If Intersect(Target, Me.Columns("B")) Is Nothing Then GoTo ExitHandler
' 多行生效 If Intersect(Target, Me.Range("5:10")) Is Nothing Then GoTo ExitHandler
' 多列生效 If Intersect(Target, Me.Range("I:J")) Is Nothing Then GoTo ExitHandler