vba Excel：或者在单元格值更改时更改单元格颜色
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/8567833/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Excel : Alternatively Change Cell Color as Cell Value Changes
提问by Mehdi LAMRANI
I have developed an Excel Real-Time Data Feed (RTD) to monitor Stock Prices as they arrive.
I Would like to find a way to change the color of a cell as prices change.
For example, a cell initially Green would turn to Red when the value changes (new price occurred on it via RTD Formula it contains) and then change back to Green when a new price arrives, and so on...
我开发了一个 Excel 实时数据馈送 (RTD) 来监控到达的股票价格。
我想找到一种随着价格变化而改变单元格颜色的方法。
例如，当值发生变化时，单元格最初的绿色会变成红色（通过它包含的 RTD 公式出现新价格），然后在新价格到达时变回绿色，依此类推...
回答by Arnoldiusss
Maybe this can get you started? I supose a event is raised when the real time data is refreshed. the concept sis to store the real time data in a variabele and check if it has changed
也许这可以让你开始？我假设刷新实时数据时会引发事件。将实时数据存储在变量中并检查它是否已更改的概念
Dim rtd As String
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
With ActiveSheet.Range("A1")
If .Value <> rtd Then
Select Case .Interior.ColorIndex
Case 2
.Interior.ColorIndex = 3
Case 3
.Interior.ColorIndex = 4
Case 4
.Interior.ColorIndex = 3
Case Else
.Interior.ColorIndex = 2
End Select
Else
.Interior.ColorIndex = 2
End If
rtd = .Value
End With
End Sub
回答by Benjamin
Sub Worksheet_Change(ByVal ChangedCell As Range)
' This routine is called whenever the user changes a cell.
' It is not called if a cell is changed by Calculate.
Dim ColChanged As Integer
Dim RowChanged As Integer
ColChanged = ChangedCell.Column
RowChanged = ChangedCell.Row
With ActiveSheet
If .Cells(RowChanged, ColChanged).Interior.ColorIndex = 19 Then
' Changed cell is red. Set it to green.
.Cells(RowChanged, ColChanged).Interior.ColorIndex = 19
Else
' Changed cell is not red. Set it to red.
.Cells(RowChanged, ColChanged).Interior.ColorIndex = 19
End If
End With
End Sub
回答by Pranav Singh
I was looking for same. My scenario was like change the color of cell when value is select from list. Each list item corresponds for a color.
我正在寻找相同的。我的场景就像从列表中选择值时更改单元格的颜色。每个列表项对应一种颜色。
What eventually worked for me is:
最终对我有用的是：
Private Sub Worksheet_Change(ByVal Target As Range)
Set MyPlage = Range("B2:M50")
For Each Cell In MyPlage
Select Case Cell.Value
Case Is = "Applicable-Incorporated"
Cell.Font.Color = RGB(0, 128, 0)
Case Is = "Applicable/Not Incorporated"
Cell.Font.Color = RGB(255, 204, 0)
Case Is = "Not Applicable"
Cell.Font.Color = RGB(0, 128, 0)
Case Else
Cell.EntireRow.Interior.ColorIndex = xlNone
End Select
Next
ActiveWorkbook.Save
End Sub
回答by orducom1
Alternatively, the most simple is this code :
或者，最简单的是这段代码：
Private Sub Worksheet_Change(ByVal Target As Range)
Target.Interior.ColorIndex = 6 ': yellow
End Sub
回答by chris neilsen
This solution reposonds to a Calculationevent. I am not entirely sure if an RTD update triggers this, so you will need to experiment.
此解决方案响应Calculation事件。我不完全确定 RTD 更新是否会触发此问题，因此您需要进行试验。
Add this code to the Worksheetmodule containing your RTD calls.
将此代码添加到Worksheet包含 RTD 调用的模块中。
It keeps a copy of the sheet data in memory from the last calculation, and on each calc compares new values.
It limits its action to cells containing your formula.
它将上次计算的工作表数据副本保存在内存中，并在每次计算时比较新值。
它将其操作限制为包含您的公式的单元格。
Option Explicit
Dim vData As Variant
Dim vForm As Variant
Private Sub Worksheet_Calculate()
Dim vNewData As Variant
Dim vNewForm As Variant
Dim i As Long, j As Long
If IsArray(vData) Then
vNewData = Me.UsedRange
vNewForm = Me.UsedRange.Formula
For i = LBound(vData, 1) To UBound(vData, 1)
For j = LBound(vData, 2) To UBound(vData, 2)
' Change this to match your RTD function name
If vForm(i, j) Like "=YourRTDFunction(*" Then
If vData(i, j) <> vNewData(i, j) Then
With Me.Cells(i, j).Interior
If .ColorIndex = 3 Then
.ColorIndex = 4
Else
.ColorIndex = 3
End If
End With
End If
End If
Next j, i
End If
vData = Me.UsedRange
vForm = Me.UsedRange.Formula
End Sub
回答by Tony Dallimore
Both the previous answer assume that Real-time data feed triggers worksheet events. I can find nothing in the RTD documents to confirm or deny this assumption. However, if it does trigger worksheet events, I would have thought that Worksheet_Change would have been the most useful since it identifies a cell that has changed.
上一个答案都假定实时数据馈送触发工作表事件。我在 RTD 文档中找不到任何内容来证实或否认这一假设。但是，如果它确实触发了工作表事件，我会认为 Worksheet_Change 将是最有用的，因为它标识了一个已更改的单元格。
The following might be worth trying. It must be placed in the code area for the relevant worksheet.
以下可能值得一试。它必须放置在相关工作表的代码区域中。
Option Explicit
Sub Worksheet_Change(ByVal ChangedCell As Range)
' This routine is called whenever the user changes a cell.
' It is not called if a cell is changed by Calculate.
Dim ColChanged As Integer
Dim RowChanged As Integer
ColChanged = ChangedCell.Column
RowChanged = ChangedCell.Row
With ActiveSheet
If .Cells(RowChanged, ColChanged).Font.Color = RGB(255, 0, 0) then
' Changed cell is red. Set it to green.
.Cells(RowChanged, ColChanged).Font.Color = RGB(0, 255, 0)
Else
' Changed cell is not red. Set it to red.
.Cells(RowChanged, ColChanged).Font.Color = RGB(255, 0, 0)
End If
End With
End Sub
