更改一个单元格的样式(更改为"坏")，当另一个单元格最近被编辑时

Question

我正在尝试制作一个简单易用的工具。

例子;当有人编辑单元格H2时(即使他们不改变它，只要他们开始编辑阶段)，我希望单元格H3立即将其样式更改为Bad(红色)。编辑H30也是一样;H58改变H59, H86改变H87。

这将通过VBA或条件格式完成吗?

Answer 1

测试Worksheet_Change事件的变化。在相应的工作表代码模块中放入如下代码:

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim addr As String
    addr = Target.Address
    Select Case addr
        Case "$H$2", "$H$30", "$H$58", "$H$86"
            Target.Offset(1).Style = "Bad"
    End Select
End Sub

Answer 2

你也可以使用:

Private Sub Worksheet_Change(ByVal Target As Range)
    If Not Intersect(Target, Range("H2, H30, H58, H$86")) Is Nothing Then Target.Offset(1).Style = "Bad"
End Sub

此外，您可能希望在用户输入任何禁止单元格时，通过应用"Alert"样式警告用户，并在未修改

的情况下删除它。

在这种情况下，您可以尝试以下代码:

Option Explicit
Const forbiddenRngAddr As String = "H2, H30, H58, H$86"
Dim oldRng As Range
Dim oldValue As Variant
Dim oldStyle As String
Private Sub Worksheet_Change(ByVal Target As Range)
    If Not Intersect(Target, Range(forbiddenRngAddr)) Is Nothing Then Target.Offset(1).style = "Bad"
End Sub
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    If Not oldRng Is Nothing Then
        If Not Intersect(oldRng, Range(forbiddenRngAddr)) Is Nothing Then
            If oldValue = oldRng.Value Then oldRng.Offset(1).style = oldStyle
        End If
    End If
    With Target
        If Not Intersect(.Cells, Range(forbiddenRngAddr)) Is Nothing Then
            oldStyle = .Offset(1).style
            .Offset(1).style = "Alert"
        End If
        Set oldRng = .Cells
        oldValue = .Value
    End With
End Sub

最后，如果您曾经设法处理在单元格徘徊后可能发生的任何用户修改，则需要更清晰的代码

这里我使用注释来存储不正确更改的单元格的旧值和样式，以便为后续用户输入检索和检查

Option Explicit
Const forbiddenRngAddr As String = "H2, H30, H58, H$86"
Dim oldRng As Range
Dim oldValue As Variant
Dim oldStyle As String
Private Sub Worksheet_Change(ByVal Target As Range)
    With Target
        If Not Intersect(.Cells, Range(forbiddenRngAddr)) Is Nothing Then
            If .Comment Is Nothing Then
                .Offset(1).style = "Bad"
                With .AddComment
                    .Visible = False
                    .Text oldValue & "|" & Target.style
                End With
            Else
                If .Text = Split(.Comment.Text, "|")(0) Then
                    .Offset(1).style = Split(.Comment.Text, "|")(1)
                    .Comment.Delete
               Else
                    .Offset(1).style = "Bad"
                End If
            End If
        End If
    End With
End Sub
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    If Not oldRng Is Nothing Then
        If Not Intersect(oldRng, Range(forbiddenRngAddr)) Is Nothing Then
            If oldValue = oldRng.Value Then oldRng.Offset(1).style = oldStyle
        End If
    End If
    With Target
        If Not Intersect(.Cells, Range(forbiddenRngAddr)) Is Nothing Then
            oldStyle = .Offset(1).style
            .Offset(1).style = "Alert"
        End If
        Set oldRng = .Cells
        oldValue = .Value
    End With
End Sub