Undo Option Lost After Worksheet Event Macro

[kwp004]

Hi,

If the user makes a certain change, I have a worksheet level macro that makes some formatting changes. The macro works well. Unfortunately, whenever it runs, the user loses the ability to Undo.


Is there either:
i) a way to keep the macro, but preserve the ability to undo, or
ii) rewrite the macro, such that a more narrow scope of events triggers it? Currently, the macro runs anytime any changes are made to the worksheet, but I really only need it to run if changes are made in range I6 to I36.

Dim irow As Long
    For irow = 6 To 35

If Range("AA" & irow) = "C" Then


    With Range("A" & irow).Resize(, 19)
        .Borders(xlEdgeTop).LineStyle = xlNone
        With .Borders(xlEdgeBottom)
            .LineStyle = xlContinuous
            .ColorIndex = 0
            .TintAndShade = 0
            .Weight = xlThin
        End With
    End With


Else
With Range("A" & irow).Resize(, 19)
        .Borders(xlEdgeTop).LineStyle = xlContinuous
        With .Borders(xlEdgeBottom)
            .LineStyle = xlContinuous
            .ColorIndex = 0
            .TintAndShade = 0
            .Weight = xlThin
        End With
    End With


End If

 

[Roxxien]

Normally you should have the first structure if I understand you.

Sub Worksheet_change()
.....
End sub

For your point 2, you can change it to this

Sub Worksheet_change(ByVal Target As Range)
If not Intersect(Target,Range("I6:I36")) Is Nothing Then
....
End If
End sub

As for your point 1, the best will be to "preserve" the old data elsewhere and make another macro to "reput" the old data at the right place.

 

[kwp004]

Worked perfectly --- thanks Roxxien!!!!