Running of Worksheet Change Macro breaks undo functionality.

[Rob Manger]

Hi all,

I have a problem, using code from this forum (from David McRitchie,
thanx Dave) I have created a Conditional formatting macro in my
worksheet change event macro. This works a treat, apart from one issue
that the users of the spreadsheet have complained about. 'Undo
Functionality' it seems that the undo functionality of any worksheet
with this macro is broken. I have heard there is no way around it.
Any ideas? Any help would be hugely appreciated. NOTE: This is in
Excel 2003 SP2, if that helps

Find below the macro I am using.

Private Sub Worksheet_Change(ByVal Target As Range)
On Error GoTo errorhandler
Dim cRange As Range
Dim cell As Range
Dim icolor As Integer
Dim target2 As String

'***************** check range ****
Set cRange = Intersect(Range(ActiveWorkbook.Names("status")),
Range(Target(1).Address))
If cRange Is Nothing Then Exit Sub
'**********************************

For Each cell In Target
If cell = "N/A" Then
icolor = 38
Else
target2 = Left(cell, 1)
Select Case target2
Case "f"
icolor = 3
Case "p"
icolor = 4
Case "b"
icolor = 46
Case "-"
icolor = 36
Case Else
icolor = 2
End Select
End If
Application.EnableEvents = False 'should be part of
Change macro
cell.Interior.ColorIndex = icolor
Application.EnableEvents = True 'should be part of Change
macro


Next cell

Exit Sub
errorhandler:
icolor = 0
Target.Interior.ColorIndex = icolor
End Sub

 

[davesexcel]

Though it is not possible to undo a macro, there are other ways around
this, maybe j-walk's tip can help you out


http://www.j-walk.com/ss/excel/tips/tip23.htm