Excel VBA开发需求：自动添加行及单元格"No"时的警告与格式设置

需求说明 #

• 任何已填充（含部分填充）的行下方自动插入新行
• 当「是否已认证？」列单元格值为"No"时，自动为该单元格着色，并在相邻单元格显示警告文本

原代码存在的问题 #

1. 自动插行逻辑仅监听第3、5列的修改，无法覆盖所有部分填充行的场景
2. 警告功能宏存在变量重复定义、固定范围遍历错误、未关联当前修改行、未处理值切换为"Yes"时的格式清除等问题
3. 条件格式在宏插入新行后，会错误保留格式至值为"Yes"的单元格

修正后的完整代码 #

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim targetRow As Long
    Dim certifiedCol As Integer ' 「是否已认证？」列，假设为F列（第6列）
    Dim warningCol As Integer ' 警告文本列，假设为I列（第9列）
    
    certifiedCol = 6
    warningCol = 9
    
    ' 禁用事件避免循环触发
    Application.EnableEvents = False
    On Error GoTo ResetEvents ' 异常时恢复事件状态
    
    ' 自动插行逻辑：当前行有填充内容则插入新行
    targetRow = Target.Row
    If Application.CountA(Rows(targetRow)) > 0 Then
        ' 避免重复插入空行
        If Application.CountA(Rows(targetRow + 1)) = 0 Then
            Rows(targetRow + 1).Insert Shift:=xlShiftDown, CopyOrigin:=xlFormatFromLeftOrAbove
        End If
    End If
    
    ' 认证状态警告处理
    If Target.Column = certifiedCol Then
        With Target
            Select Case .Value
                Case "No"
                    ' 设置单元格填充色（浅红）
                    .Interior.Color = RGB(255, 199, 206)
                    ' 写入警告文本并设置字体颜色
                    Cells(.Row, warningCol).Value = "不可使用"
                    Cells(.Row, warningCol).Font.Color = RGB(156, 0, 6)
                Case "Yes"
                    ' 清除填充色与警告文本
                    .Interior.ColorIndex = xlColorIndexNone
                    Cells(.Row, warningCol).ClearContents
            End Select
        End With
    End If
    
ResetEvents:
    Application.EnableEvents = True
End Sub

代码关键说明 #

• 自动插行优化：通过Application.CountA判断当前行是否有填充内容，只要存在非空单元格就插入新行，同时检查下一行是否为空避免重复插入；使用Application.EnableEvents防止插入行时循环触发Change事件。
• 警告功能修复：直接监听「是否已认证？」列的修改，针对当前操作单元格处理：值为"No"时设置填充色和警告文本，值为"Yes"时清除格式和警告，适配宏新增的行，解决原条件格式残留问题。
• 灵活调整：若「是否已认证？」列或警告列不是F/I列，修改certifiedCol和warningCol的列号即可；填充色、警告文本可根据需求自行调整。

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