Highlighting Cells That Change

Question

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!

Not sure what you're doing, but perhaps: duplicate your data sheet, then on the copy, set up conditional formatting, where say `=Sheet1Copy!A1<>Sheet1!A1`? But that'll show a difference between sheets, not *necessarily* updated cells between saves/changes while you're working. — BruceWayne, Feb 23 '23 at 21:56

jacouh · Answer 1 · 2023-02-24T19:08:06.450

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 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.

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

At the second step, we add code in Worksheet private module, highlighting dynamically the ActiveCell/Selection, as you want:

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

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

Spectral Instance · Answer 2 · 2023-02-24T03:30:22.300

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.

Highlighting Cells That Change

2 Answers2