突出显示更改的单元格

huangapple go评论78阅读模式
英文:

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个步骤:

  1. 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
  1. 在第二步,我们在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:

  1. In the ThisWorkbook private module, define a public member gOldTarget. And we add code to highlight the Selection with focus immediately after openning Excel doc in Workbook_Open(). As well as to delete the highliting when quitting Excel in Workbook_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>

  1. 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=&quot;A1&quot;

(iii) update your Worksheet_Change() procedure

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Count &lt;&gt; 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 &lt;&gt; 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  &#39;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.

突出显示更改的单元格
突出显示更改的单元格

huangapple
  • 本文由 发表于 2023年2月24日 05:45:45
  • 转载请务必保留本文链接:https://go.coder-hub.com/75550638.html
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定