Excel VBA: Trigger macro on cut/paste/delete/insert events

Question

I have a conditional formatting rule defined as macro, which deletes the old rules and replaces them with updates ones:

Sub setCondFormat()
    Set Table = ActiveSheet.ListObjects("Rules")
    Table.Range.FormatConditions.Delete
    Set Attribute = Table.ListColumns("Attribute")
    With Attribute.DataBodyRange.FormatConditions _
    .Add(xlExpression, xlEqual, "=ISEMPTY(A2)")
        With .Interior
            .ColorIndex = 0
        End With
    End With
End Sub

The conditional formatting in Excel needs to be updated. Otherwise the cell ranges in the rules get fragmented.

Let's say you have two rules:

1. Make $A$1:$A$30 red
2. Make $B$1:$B$30 blue Now select A10:B10 and copy/paste that to A20:B20.
   What Excel will do is to delete the conditional formatting.

For A20:B20 from the rules that applied to those cells and add new rules that have the formatting for A20:B20. You end up with four rules:

1. Make =$A$20 red
2. Make =$B$20 blue
3. Make =$A$1:$A$19,$A$21:$A$30 red
4. Make =$B$1:$B$19,$B$21:$B$30 blue

This happens, when the table structure gets changed through cut/paste/delete/insert events.

How to trigger the above VBA macro on cut/paste/delete/insert events?

Answer 1

You could use a shortcut for your macro

VBA event trigger on copy?

If you don't want to go this way you'll need to use the Windows API:

Is there any event that fires when keys are pressed when editing a cell?

Answer 2

The solution I found is create a new Sheet with the content of your table when you open the Workbook. First you need to create a Module with the Public Variables.

Public OldRange As Range
Public NewRange As Range
Public Table As ListObject

Then, use the event Open of your Workbook.

Private Sub Workbook_Open()
    Dim sh As Worksheet
    Dim address As String

    For Each sh In Worksheets
        If sh.Name = "DATA" Then
            Worksheets("DATA").Activate
            ActiveSheet.Delete
        End If
    Next

    ActiveWorkbook.Sheets.Add After:=Worksheets(Worksheets.Count)
    ActiveSheet.Name = "DATA"


    Set sh = ActiveWorkbook.Sheets("Plan1")
    sh.Activate

    Set Table = ActiveSheet.ListObjects("Rules")
    Set OldRange = Table.Range
    address = Table.Range.address

    Table.Range.Copy

    Set sh = ActiveWorkbook.Sheets("DATA")
    sh.Activate
    Range(address).PasteSpecial (xlPasteAll)
End Sub

And then, use the event Worksheet_Change to verify the content of your original table with the earlier saved table.

Private Sub Worksheet_Change(ByVal Target As Range)
    Set Table = ActiveSheet.ListObjects("Rules")

    If Intersect(Target, Table.Range) Is Nothing Then Exit Sub 'this will guarantee that the change made in your sheet is in your desired table

    Set NewRange = Table.Range

    Dim rng As Range
    Dim rngaddr As String
    Dim TableChanged As Boolean

    TableChanged = False

    For Each rng In NewRange
        rngaddr = rng.address
        If rng.Value <> ActiveWorkbook.Sheets("DATA").Range(rngaddr).Value Then
             'do something
             TableChanged = True
        End If
    Next
End Sub

Remember: you need to save the content of your table every time you changed it.