|
- Private Sub Worksheet_Change(ByVal Target As Range)
- r = Target.Row '当前行
- c = Target.Column '当前列
- mc = Cells(2, 256).End(xlToLeft).Column '最大列
- r1 = 3 + Int((r - 3) / 4) * 4 '令数所在的行
- ls = Cells(r1, 3) '令数
- arr = Range(Cells(r, 5), Cells(r, mc))
-
- If Cells(2, c) = "计划" Then '计划栏
- For j = 2 To UBound(arr, 2) Step 2
- s1 = s1 + arr(1, j) '计划数
- s2 = s2 + arr(1, j - 1) '完成数
- Next
- If s1 + s2 > ls Then
- MsgBox "计划数+完成数大于令数"
- Application.EnableEvents = False
- Target.ClearContents
- Application.EnableEvents = True
- Exit Sub
- End If
- End If
-
- If Cells(2, c) = "完成" Then '完成
- For j = 1 To UBound(arr, 2) Step 2
- s = s + arr(1, j) '完成数
- Next
- If s > ls Then
- MsgBox "完成数大于令数"
- Application.EnableEvents = False
- Target.ClearContents
- Application.EnableEvents = True
- Exit Sub
- End If
-
- If Cells(r, 4) = "组装" Then '组装行确定条件格式
- If s = ls Then
- Cells(r1, 1).Resize(4, 3).Interior.Color = vbRed
- Else
- Cells(r1, 1).Resize(4, 3).Interior.ColorIndex = 0
- End If
- End If
- End If
- End Sub
复制代码 |
|