VBA 代码突出显示修改的单元格并在退出前清除突出显示
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/25209976/
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
VBA Code to highlight modified cells and clear highlight before exit
提问by Joe Nickerson
I need to be able to highlight cells that are modified and before the sheet is closed, remove the highlighting. Typically the cells that will be changed are always in one column. I've been reading the developer reference and playing in vba all day. Despite my best efforts I can't seem to figure this one out.
我需要能够突出显示已修改的单元格，并在关闭工作表之前删除突出显示。通常，将被更改的单元格总是在一列中。我整天都在阅读开发人员参考并在 vba 中玩游戏。尽管我尽了最大的努力，但我似乎无法弄清楚这一点。
I have a string of code that highlights any targeted cell:
我有一串代码可以突出显示任何目标单元格：
Target.Interior.Color = RGB(181, 244, 0)
I was thinking of making an array that recorded targeted cells and on a BeforeClose event, clearing the formatting based on the arrays values. Is that even possible?
我正在考虑制作一个记录目标单元格和 BeforeClose 事件的数组，根据数组值清除格式。这甚至可能吗？
Thanks in advance!
提前致谢！
回答by Gary's Student
Include the following event macros in the worksheet code area:
在工作表代码区域中包含以下事件宏：
Private Sub Worksheet_Change(ByVal Target As Range)
Target.Interior.Color = RGB(181, 244, 0)
End Sub
Private Sub Worksheet_Deactivate()
Cells.Interior.ColorIndex = xlNone
End Sub
回答by cronos2546
Public workrange As Range
Private Sub Worksheet_Change(ByVal Target As Range)
ThisWorkbook.Names.Add Name:="MyRangeName", RefersTo:="Sheet1!$A"
Set workrange = Range("MyRangeName")
Target.Interior.Color = RGB(181, 244, 0)
Set workrange = Union(workrange, Target.Address)
End Sub
Private Sub Workbook_BeforeClose(Cancel As Boolean)
Dim cell As Variant
For Each cell In workrange
If cell.Interior.Color <> RGB(0, 0, 0) Then
cell.Interior.Color = RGB(0, 0, 0)
End If
Next
End Sub
should do what you want, I believe.
应该做你想做的，我相信。
回答by Zeno
I have tested the following code and it works on my side:
我已经测试了以下代码，它对我有用：
Private Sub Worksheet_Change(ByVal Target As Range) 'Add this to each required worksheet
Target.Interior.Color = RGB(181, 244, 0)
If HighlightedCells = "" Then
HighlightedCells = Target.Address
Else
HighlightedCells = HighlightedCells & "," & Target.Address
End If
End Sub
Private Sub Workbook_BeforeClose(Cancel As Boolean) 'For the Workbook. Alter and repeat the line below for each required worksheet
ThisWorkbook.Worksheets("Sheet1").Range(HighlightedCells).Style = "Normal"
End Sub
Global HighlightedCells As String 'Add this to a new module
