在失去焦点的Visual Basic Excel颜色单元格

Question

我需要在excel中制作一个VBA脚本，当一个值大于或小于另一个值至少10%时，该脚本会显示2个单元格

Private Sub Worksheet_Change(ByVal Target As Range)
 Application.EnableEvents = False
 If Target.Address = aprx_Lns Then
 If aprx_Lns > aprx2_Lns * 0.1 Then
 aprx_Lns.Interior.Color = Hex(FFFF00)
 aprx2_Lns.Interior.Color = Hex(FFFF00)
 ElseIf aprx_Lns < aprx2_Lns * 0.1 Then
 aprx_Lns.Interior.Color = Hex(FFFF00)
 aprx2_Lns.Interior.Color = Hex(FFFF00)
 End If
 End If
 Application.EnableEvents = True
 End Sub
 Private Sub Worksheet_Change2(ByVal Target As Range)
 Application.EnableEvents = False
 If Target.Address = aprx2_Lns Then
 If aprx_Lns > aprx2_Lns * 0.1 Then
 aprx_Lns.Interior.Color = Hex(FFFF00)
 aprx2_Lns.Interior.Color = Hex(FFFF00)
 ElseIf aprx_Lns < aprx2_Lns * 0.1 Then
 aprx_Lns.Interior.Color = Hex(FFFF00)
 aprx2_Lns.Interior.Color = Hex(FFFF00)
 End If
 End If
 Application.EnableEvents = True
 End Sub

我做错了什么?两个单元格都没有将颜色更改为所选颜色，即使在我使if语句的值为真之后。
我对VBA几乎一无所知，所以任何解释也会很好。谢谢!

Answer 1

根据我上面的评论，让我们将逻辑合并到单个事件处理程序中。

同样，使用已命名的区域/单元格是好的，但是您需要正确地引用它们。名称本身在VBA中没有意义，除非它被限定为一个显式的范围。将名称作为字符串传递，如Range("aprx_Lns")等。

注意此代码只会在您直接更改这两个单元格中的一个的值时触发。这意味着，如果这些单元格包含引用其他单元格的公式，并且其他单元格更改，则不会出现高亮显示。

修订,简化的

 Private Sub Worksheet_Change(ByVal Target As Range)
 Dim aprx_Lns As Range
 Dim aprx_Lns2 As Range
 Dim difference As Double
 Dim diffRatio As Double
 Set aprx_Lns = Range("aprx_Lns")    '## Modify as needed
 Set aprx2_Lns = Range("aprx2_Lns")   '## Modify as needed
 Application.EnableEvents = False
 If Target.Address = aprx_Lns.Address Or Target.Address = aprx2_Lns.Address Then

    difference = Abs(aprx_Lns) / Abs(aprx2_Lns)
    '## compute the absolute difference as a ratio
    diffRatio = Abs(1 - difference)
    If diffRatio >= 0.1 Then
    '### if the cell values differ by +/- 10%, then highlight them
         aprx_Lns.Interior.Color = 65535 'vbYellow
         aprx2_Lns.Interior.Color = 65535 'vbYellow
    Else
    '### otherwise, unhighlight them:
        aprx_Lns.Interior.Color = xlNone
        aprx2_Lns.Interior.Color = xlNone
    End If
End If
Application.EnableEvents = True
End Sub