单元格增加时,绿色闪烁,减少时为红色 [英] 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.
这篇关于单元格增加时,绿色闪烁,减少时为红色的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!