How to highlight changes made on a specific sheet using VBA?

To highlight changes made on a specific Excel sheet using VBA, you can use the `Worksheet_Change` event. This event is triggered every time a change is made to a cell within the worksheet. You can write a macro to highlight the changed cell by modifying its background color. Here’s how you can do it:

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim Cell As Range
    Dim WatchRange As Range
    Dim IntersectRange As Range
     
    ' Define the range you want to monitor for changes
    Set WatchRange = Me.Range("A1:Z100") ' Change this to your target range
    
    ' Check for intersections with the target range
    Set IntersectRange = Intersect(Target, WatchRange)
    
    If Not IntersectRange Is Nothing Then
        For Each Cell In IntersectRange
            ' Highlight with a specific color, e.g., Light Yellow
            Cell.Interior.Color = RGB(255, 255, 153)
        Next Cell
    End If
End Sub

Open the VBA Editor: Press `ALT` + `F11` to open the Visual Basic for Applications editor.
Access the Specific Sheet’s Code Window:

In the Project Explorer (the panel usually on the left), find `VBAProject (YourWorkbookName)`.
Under this, find `Microsoft Excel Objects`, and locate the sheet where you want to track changes (e.g., `Sheet1`).

Enter the Code:

Double-click on the sheet name to open the code window for that specific sheet.
Enter the following code in the code window:

Customize the Range and Color:

Adjust `WatchRange` to the range of cells you want to monitor for changes.
You can change the `RGB(255, 255, 153)` value to another RGB color code if you want a different highlight color.

Save your Workbook:

Save your workbook as a macro-enabled workbook with the `.xlsm` extension.

Test the Code:

Return to Excel and make some changes in the specified sheet and cells. You should see the background color of the changed cells update to the color you specified.

By using this VBA script, changes made within the defined range in your worksheet are automatically highlighted, thus enabling better tracking of alterations.

facebooklinkedin