Undo changes function in Excel when using VBA in workbook

Question

I have the following VBA code in my excel workbook:

    Private Sub Worksheet_Change(ByVal Target As Range)
Target.Font.ColorIndex = 3
    If Target.Column = 1 Then
    Target.Interior.ColorIndex = 2
End If
End Sub

As I have found out, the undo function is lost. I have searched online and found this:

Building Undo Into an Excel VBA Macro However this will only undo the changes the last VBA code performed and not any other changes that are made without the use of VBA code in my workbook e.g. undoing a copy and paste

I have added this in the code but still no luck!

Application.OnUndo "Primary Macro", "UndoPrimary"

This is the message that appears on my mac which is a similar message to when I use my Windows7 PC

http://s16.postimg.org/m8hi9i4qd/error.jpg

Answer 1

Why don't you save states?

'Create global arrays to store your colors in
Dim myColors() As String
Dim myFontColors() As String

'Create a function to save your state
Private Function SaveState(ByVal Target As Range)
    Dim x, index As Long
    index = 0

    'Make your arrays the same size as the target Range
    ReDim myColors(Target.Count)
    ReDim myFontColors(Target.Count)

    For Each x In Target.Cells
        myColors(index) = x.Interior.Color
        myFontColors(index) = x.Font.Color
        index = index + 1
    Next x
End Function

'Create a function to load your state
Private Function LoadState(ByVal Target As Range)
    Dim x, index As Long
    index = 0
    'Check to make sure the arrays have data in them first
    If UBound(myColors) = 0 Or Ubound(myFontColors) = 0 Then Exit Function

    For Each x In Target.Cells
        x.Interior.Color = myColors(index)
        x.Font.Color = myFontColors(index)
        index = index + 1
    Next x
End Function

Sub TestIt()
    Dim myRange As Range

    Set myRange = ActiveSheet.Range("A1:A12")
    SaveState myRange

    'I use a different column to see what's happening
    Set myRange = ActiveSheet.Range("B1:B12")
    LoadState myRange
End Sub