单元格增加时，绿色闪烁，减少时为红色 [英] Flashing green when cell value increases, red when decreases

问题描述

我的excel表中有一个feed，如果该值相应变化，则要使单元格闪烁绿色/红色。有人可以帮忙吗？谢谢。

I have a feed in my excel sheet and want the cell to flash green / red if the value changes up or down respectivey. Can someone help on this? Thanks.

Private Sub Worksheet_Change(ByVal Target As Range)
   Dim KeyCells As Range

   Set KeyCells = Range("B1:B27")

   If Not Application.Intersect(KeyCells, Range(Target.Address)) _
          Is Nothing Then

       Target.Interior.ColorIndex = 36

   End If
End Sub

感谢您的帮助

推荐答案

正如约翰·布斯托斯所说，您需要另一列，包含单元格的前一个值。为了比较以辨别目标现在是高还是低，这个值是必要的。

As John Bustos said, you need another column that houses the previous value of the cell. This value is necessary in order to do comparison to discern if the target is now higher or lower.

为了实现闪光，您需要在代码中设置暂停，而不会冻结主UI。这个代码没有被测试或保证按照你想要的工作，但它应该是一个非常好的起点。

In order to achieve a flash, you need to set a pause in the code without freezing up the main UI. This code isn't tested or guaranteed to work as you want, but it should serve as a very good starting point.

代码

Private Sub Worksheet_Change(ByVal Target As Range)
   Dim KeyCells As Range
   Set KeyCells = Range("B1:B27")

   If Not Application.Intersect(KeyCells, Range(Target.Address)) _
          Is Nothing Then
        If Target.Value > Cells(Target.Row, 5).Value Then
            'flash green
            Target.Interior.ColorIndex = 10
            Pause 0.5
            Target.Interior.ColorIndex = 2
            Pause 0.5
            Target.Interior.ColorIndex = 10
        ElseIf Target.Value < Cells(Target.Row, 5).Value Then
            'flash red
            Target.Interior.ColorIndex = 3
            Pause 0.5
            Target.Interior.ColorIndex = 2
            Pause 0.5
            Target.Interior.ColorIndex = 3
        End If
        Cells(Target.Row, 5).Value = Target.Value
   End If
End Sub

'Pauses execution without holding up main UI thread
Public Function Pause(NumberOfSeconds As Variant)
    On Error GoTo Error_GoTo
    Dim PauseTime As Variant
    Dim Start As Variant

        PauseTime = NumberOfSeconds
        Start = Timer
        Do While Timer < Start + PauseTime
            DoEvents
        Loop

Exit_GoTo:
       On Error GoTo 0
       Exit Function
Error_GoTo:
       Debug.Print Err.Number, Err.Description, Erl
      GoTo Exit_GoTo
End Function

打开Visual Basic编辑器（VBE）并双击Sheet 1，然后粘贴上面的代码。现在，您将注意到，您可以在目标范围 B1：B27 中更改值，并将其放置在列中 E 。您可以通过在 Cells（Target.Row，5）.Value 中替换 5

Open the Visual Basic Editor (VBE) and double click Sheet 1, then paste the above code. Now you'll notice as you change the values within the targeted range B1:B27 a value with be placed in column E. You can change this in the code above by replacing 5 in Cells(Target.Row, 5).Value with whatever column you'd like.

现在，随着值的增加或减少，单元格将闪烁以反映更改。

Now as the values increase or decrease, the cell will flash to reflect the change.

在此SO中找到暂停功能发布。

这篇关于单元格增加时，绿色闪烁，减少时为红色的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！