英文:
Highlighting Cells That Change
问题
我想要突出显示Excel表格中更改的单元格变成黄色。目前,我有以下代码:
Private Sub Worksheet_Change(ByVal Target As Range)
Target.Interior.ColorIndex = 6
End Sub
这个方法非常有效且达到了预期的目的,但存在一个明显的问题。我无法还原更改(Ctrl + Z 不起作用),如果我将单元格更改回原始内容,单元格仍然保持黄色,因为它被识别为更改,导致无限循环。我可以手动选择“无填充”来删除黄色填充,但我希望这是自动的。如果我在表格中犯了错误并希望返回到单元格的原始内容,我希望单元格没有填充,而不必手动操作。
是否有办法解决这个问题?
非常感谢您的任何帮助!
英文:
I would like to highlight any cell that changes within an Excel sheet yellow. Currently, I have the following code:
Private Sub Worksheet_Change(ByVal Target As Range)
Target.Interior.ColorIndex = 6
End Sub
This works very well and serves the intended purpose, but has one glaring issue. I cannot revert the changes (ctrl + z does not work) and if I change the cell back to it's original content, the cell remains yellow, as it is being recognized as a change, leading to an endless loop. I can manually select 'no fill' to remove the yellow fill, but I would like this to be automatic. In case I were to make a mistake in the sheet and want to go back to the cell's original content, I would like the cell to have no fill without manually doing so.
Is there any way around this issue?
Any help is greatly appreciated, thank you!
答案1
得分: 3
这可能更适合您的用途,使用Worksheet_SelectionChange()
而不是Worksheet_Change()
,分为2个步骤:
- 在
ThisWorkbook
私有模块中,定义一个名为gOldTarget的公共成员。我们在Workbook_Open()
中添加代码,在打开Excel文档后立即突出显示Selection
的内容,同时在Workbook_BeforeClose()
中退出Excel时删除高亮显示。这样,黄色单元格设置不会保存在最终的Excel文档中。
Option Explicit
Public gOldTarget As Range
Private Sub Workbook_Open()
If (TypeName(Selection) = "Range") Then
Selection.Interior.ColorIndex = 6
Set gOldTarget = Selection
End If
End Sub
Private Sub Workbook_BeforeClose(Cancel As Boolean)
'
' 在保存为doc.xlsm之前恢复旧单元格:
'
If (Not gOldTarget Is Nothing) Then
gOldTarget.Interior.ColorIndex = xlColorIndexNone
End If
End Sub
- 在第二步,我们在
Worksheet
私有模块中添加代码,动态突出显示当前活动单元格/选择,如您所需:
Option Explicit
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
'
' 突出显示当前单元格:
'
Target.Interior.ColorIndex = 6
'
' 恢复旧单元格:
'
If (Not ThisWorkbook.gOldTarget Is Nothing) Then
ThisWorkbook.gOldTarget.Interior.ColorIndex = xlColorIndexNone
End If
'
' 保存当前的目标:
'
Set ThisWorkbook.gOldTarget = Target
End Sub
使用Worksheet_SelectionChange()
,它会突出显示当前具有焦点的单元格。当您退出单元格时,高亮显示会被取消。
有关Worksheet_SelectionChange()
的详细信息,请参考:https://learn.microsoft.com/en-us/office/vba/api/excel.worksheet.selectionchange
英文:
This might be better for your usage, using Worksheet_SelectionChange()
instead of Worksheet_Change()
in 2 steps:
- In the
ThisWorkbook
private module, define a public member gOldTarget. And we add code to highlight theSelection
with focus immediately after openning Excel doc inWorkbook_Open()
. As well as to delete the highliting when quitting Excel inWorkbook_BeforeClose()
. So that the yellow cell setting is not saved in the final Excel doc.
<pre></code>
Option Explicit
Public gOldTarget As Range
Private Sub Workbook_Open()
If (TypeName(Selection) = "Range") Then
Selection.Interior.ColorIndex = 6
Set gOldTarget = Selection
End If
End Sub
Private Sub Workbook_BeforeClose(Cancel As Boolean)
'
' restore the old cell before saving into doc.xlsm:
'
If (Not gOldTarget Is Nothing) Then
gOldTarget.Interior.ColorIndex = xlColorIndexNone
End If
End Sub
</code></pre>
- At the second step, we add code in
Worksheet
private module, highlighting dynamically the ActiveCell/Selection, as you want:
<pre></code>
Option Explicit
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
'
' highlight the current cell:
'
Target.Interior.ColorIndex = 6
'
' restore the old cell:
'
If (Not ThisWorkbook.gOldTarget Is Nothing) Then
ThisWorkbook.gOldTarget.Interior.ColorIndex = xlColorIndexNone
End If
'
' save the current target:
'
Set ThisWorkbook.gOldTarget = Target
End Sub
</code></pre>
With Worksheet_SelectionChange(), it highlights the current cell with focus. When you quit the cell the highliting is suppressed.
Ref. https://learn.microsoft.com/en-us/office/vba/api/excel.worksheet.selectionchange
答案2
得分: 2
全局变量在编程中通常不被推荐使用,但这是一个你可以利用的情况之一:
(i) 在工作表模块的开头声明该变量,例如:
Public prevTarget As String
(ii) 初始化它,可以在“立即窗口”中手动执行,或者在 Workbook_Open()
过程中以编程方式执行,例如:
Sheet1.prevTarget = "A1"
(iii) 更新你的 Worksheet_Change() 过程
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Count <> 1 Then Exit Sub
Range(prevTarget).Interior.ColorIndex = xlNone
Target.Interior.ColorIndex = 6
prevTarget = Target.Address
End Sub
编辑以添加包含历史记录的代码
(i) 在工作表模块的开头声明2个公共变量,例如:
Public prevTarget As String, logCount As Long
(ii) 在插入的模块的开头声明第3个公共变量,例如:
Public history() As String
(iii) 更新 Worksheet_Change() 过程如下:
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Count <> 1 Then Exit Sub
Application.EnableEvents = False
Application.ScreenUpdating = False
logCount = logCount + 1
ReDim Preserve history(1 To 2, 1 To logCount)
Dim newVal As String
newVal = Target.Formula
Application.Undo
history(1, logCount) = Target.Address
history(2, logCount) = Target.Formula
Target.Formula = newVal
Target.Offset(1, 0).Select ' 模拟按下 Enter 键的效果(由 Undo 方法调用阻止)
Application.EnableEvents = True
Range(prevTarget).Interior.ColorIndex = xlColorIndexNone
Target.Interior.ColorIndex = 6
prevTarget = Target.Address
Application.ScreenUpdating = True
End Sub
而且,每当你想查看这个历史记录时,可以执行以下操作:
Sub historyLog()
ActiveCell.Resize(UBound(history, 2), 2).Value2 = Application.Transpose(history)
End Sub
如果你快速连续进行多次更改,你将会注意到数据输入现在是“粘滞”的,但这是不可避免的,因为需要调用 Application.Undo()
方法来维护更改历史记录。
英文:
Global variables are frowned upon in programming but this is one area where you can use one to your advantage:
(i) declare the variable at the start of the worksheet module, e.g.
Public prevTarget As String
(ii) initialise it, either manually in the Immediate Window, or programatically in a Workbook_Open()
procedure, e.g.
Sheet1.prevTarget="A1"
(iii) update your Worksheet_Change() procedure
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Count <> 1 Then Exit Sub
Range(prevTarget).Interior.ColorIndex = xlNone
Target.Interior.ColorIndex = 6
prevTarget = Target.Address
End Sub
Edited to add history-inclusive code
(i) Declare 2 public variables at the start of the worksheet module, e.g.
Public prevTarget As String, logCount As Long
(ii) Declare a 3rd public variable at the start of an inserted module, e.g.
Public history() As String
(iii) Update the Worksheet_Change() procedure as follows
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Count <> 1 Then Exit Sub
Application.EnableEvents = False
Application.ScreenUpdating = False
logCount = logCount + 1
ReDim Preserve history(1 To 2, 1 To logCount)
Dim newVal As String
newVal = Target.Formula
Application.Undo
history(1, logCount) = Target.Address
history(2, logCount) = Target.Formula
Target.Formula = newVal
Target.Offset(1, 0).Select 'replicate the effect of cursor movement on Enter (prevented by call to Undo method)
Application.EnableEvents = True
Range(prevTarget).Interior.ColorIndex = xlColorIndexNone
Target.Interior.ColorIndex = 6
prevTarget = Target.Address
Application.ScreenUpdating = True
End Sub
and, whenever you want to view this history, you can execute use this
Sub historyLog()
ActiveCell.Resize(UBound(history, 2), 2).Value2 = Application.Transpose(history)
End Sub
If you make a lot of changes in quick succession then you will notice that data entry is now 'sticky', but that is inevitable because of the call to the Application.Undo()
method, which is required to maintain the change history.
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库,让每个人都能够通过互相帮助和分享经验来进步。
评论