VBA If-ElseIf语句返回异常及单元格自动变更问题求助

你遇到的单元格自动变更问题，核心出在single_change过程里的day_cell赋值错误，加上缺少事件禁用逻辑导致的连锁触发：

1. day_cell指向错误：
   当你修改O列（Production列，对应changed_cell.Column=15）时，这段代码：

ElseIf (col_num - 1) Mod 4 = 0 Then
 Set sales_cell = changed_cell.Offset(, -1)
 Set production_cell = changed_cell
 Set day_cell = production_cell.Offset(, -1) ' 这里错了！

把day_cell设置成了production_cell左移1列（也就是N2），而不是正确的右移1列（P2）。所以当执行day_cell = "Green"时，实际修改的是N2单元格，导致你看到的Rollup被替换成Green的异常。

2. 未禁用事件触发：
   在Worksheet_Change事件中修改单元格时，会再次触发Worksheet_Change事件，可能引发重复执行或意外的递归行为，加重问题。

针对这两个问题，做以下修改：

1. 修正day_cell的指向 #

在single_change过程的第二个条件分支里，把day_cell的赋值改成右移1列：

Set day_cell = production_cell.Offset(, 1) ' 替换原来的Offset(, -1)

2. 添加事件禁用与恢复逻辑 #

在修改单元格前禁用Excel的事件触发，修改完成后再恢复，避免递归触发。需要在Worksheet_Change中处理：

Private Sub Worksheet_Change(ByVal Target As Range)
    Application.EnableEvents = False ' 禁用事件
    On Error Resume Next ' 确保出错时也能恢复事件
    Call Module1.single_change(Target)
    Application.EnableEvents = True ' 恢复事件
End Sub

3. 优化空值判断（可选） #

原代码里的sales_cell = " "是判断单个空格，建议改成Trim(sales_cell.Value) = ""来兼容真正的空单元格或空白字符。

模块中的single_change过程： #

Public Sub single_change(changed_cell As Range)
 Dim sales_cell As Range
 Dim production_cell As Range
 Dim day_cell As Range
 Dim col_num As Integer
 col_num = changed_cell.Column
 If changed_cell.Column < 14 Then 'Dont do anything before Col N
 Exit Sub
 Else
 col_num = changed_cell.Column - 14
 End If
 If col_num Mod 4 = 0 Then
 Set sales_cell = changed_cell
 Set production_cell = changed_cell.Offset(, 1)
 Set day_cell = production_cell.Offset(, 1)
 ElseIf (col_num - 1) Mod 4 = 0 Then
 Set sales_cell = changed_cell.Offset(, -1)
 Set production_cell = changed_cell
 Set day_cell = production_cell.Offset(, 1) ' 修复这里的偏移方向
 Else 'Dont do anything between Col N,O and their repeated values
 Exit Sub
 End If
 On Error GoTo multiple_changes
 ' 优化空值判断
 If Trim(sales_cell.Value) = "" And Trim(production_cell.Value) = "" Then
 day_cell.Value = ""
 ElseIf sales_cell.Value = "Green" And production_cell.Value = "Rollup" Then
 day_cell.Value = "Green"
 ElseIf sales_cell.Value = "Rollup" And production_cell.Value = "Rollup" Then
 day_cell.Value = "Rollup"
 ElseIf sales_cell.Value = "Rollup" And production_cell.Value = "Green" Then
 day_cell.Value = "Green"
 ElseIf sales_cell.Value = "Rollup" And production_cell.Value = "Yellow" Then
 day_cell.Value = "Yellow"
 ElseIf sales_cell.Value = "Rollup" And production_cell.Value = "Red" Then
 day_cell.Value = "Red"
 ElseIf sales_cell.Value = "Rollup" And production_cell.Value = "Overdue" Then
 day_cell.Value = "Overdue"
 Else 'Do nothing
 End If
 Exit Sub
multiple_changes:
 Dim i As Long
 Dim A As Long
 Dim B As Long
 Dim c As Long
 A = 14
 B = 15
 c = 16
 Do While A <= 42
 i = 2
 Do Until Len(Cells(i, A)) = 0
 If Trim(Cells(i, A).Value) = "" And Trim(Cells(i, B).Value) = "" Then
 Cells(i, c).Value = ""
 ElseIf Cells(i, A).Value = "Green" And Cells(i, B).Value = "Rollup" Then
 Cells(i, c).Value = "Green"
 ElseIf Cells(i, A).Value = "Rollup" And Cells(i, B).Value = "Rollup" Then
 Cells(i, c).Value = "Rollup"
 ElseIf Cells(i, A).Value = "Rollup" And Cells(i, B).Value = "Green" Then
 Cells(i, c).Value = "Green"
 ElseIf Cells(i, A).Value = "Rollup" And Cells(i, B).Value = "Yellow" Then
 Cells(i, c).Value = "Yellow"
 ElseIf Cells(i, A).Value = "Rollup" And Cells(i, B).Value = "Red" Then
 Cells(i, c).Value = "Red"
 ElseIf Cells(i, A).Value = "Rollup" And Cells(i, B).Value = "Overdue" Then
 Cells(i, c).Value = "Overdue"
 Else
 End If
 i = i + 1
 Loop
 A = A + 4
 B = A + 1
 c = A + 2
 Loop
End Sub

工作表中的代码： #

Private Sub Worksheet_Change(ByVal Target As Range)
    Application.EnableEvents = False
    On Error Resume Next
    Call Module1.single_change(Target)
    Application.EnableEvents = True
End Sub

修改后，当你在N2输入Rollup、O2输入Green时，P2会正确返回Green，N2不会被意外修改。同时事件禁用逻辑也避免了不必要的递归触发，让宏运行更稳定。

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