Excel 2003 VBA track cell changes with fill color

[river]

I'm somewhat of a newbie at working with VBA code. I have a workbook where the users can edit cells that have a fill background of "rose". When they clear the field and enter a new value then hit "enter", the color changes to blue and a message box pops up. However, I have to assume that the users are naive and may clear the box in error, and wish to return to the original value (with a rose fill color).
With my code, this isn't possible using the "undo" button. If undoing via VBA can't be added I'd like some ideas on how to alert the user upon entering the cell to note the original value before clearing it and enter his/her own value.
Here's my code:

Private Sub Worksheet_Change(ByVal Target As Range)
Worksheets("Expenses").Range("B5").Value = Range("K49")
'This lookes for changes in pink cells and chenges them to blue
If Target.Interior.ColorIndex = 38 Then
MsgBox "Add a comment below. Make sure this is your final change. If you made a mistake and want to go back to the original value, exit and re-open the workbook."
Target.Interior.ColorIndex = 37
End If
End Sub

Thank you for any and all postings!

 

[Ralajer]

You can undo from within the a macro just not after a macro. The following code will prompt the user to confirm that they want to make the change. If yes it changes the fill color and if no it undoes there change. I wasn't sure how handle the Worksheets("Expenses").Range("B5").Value = Range("K49") line so I added under the if yes condition.

Private Sub Worksheet_Change(ByVal Target As Range)
    '// Color 38 = Rose
    '// Color 37 = Light blue
    Dim response As Integer
    '// Turn off Events to prevent Undo from trigger this sub recursively
    Application.EnableEvents = False
    
    If Target.Interior.ColorIndex = 38 Then
        response = MsgBox( _
                    Prompt:="Make sure this is your final change." & _
                    " It cannot be undone do you wish to proceed." & vbCr & _
                    " • If yes add comment below.", _
                    Buttons:=vbYesNo)
        If response = vbYes Then
            Target.Interior.ColorIndex = 37
            Worksheets("Expenses").Range("B5").Value = Range("K49")
        Else
            Application.Undo
        End If
    End If
    '// Turn on Events
    Application.EnableEvents = True
End Sub

 

[river]

Thank you so much, Rob! I did a little additional coding to include changing the cell pattern and it's color as follows, if anyone wants to see the addition. It comes in handy when working with a black and white printer:

If response = vbYes Then
Target.Interior.ColorIndex = 37
Target.Interior.Pattern = xlLightVertical
Target.Interior.PatternColorIndex = 15
End If

etc.