Making Undo button active

[Bhuktar S]

I have put the following in excel sheet code
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Range("B2") > 20 Then
Range("B2").Interior.ColorIndex = 4
Else: Range("B2").Interior.ColorIndex = 2
End If
End Sub
If I edit any cell, I cannot undo, since the undo button on the men
bar becomes inactive.
How to overcome this

 

[Frank Kabel]

Hi
AFAIK no chance as this macro disabled the Undo function

 

[Lady Layla]

Save the file before editing any cell, that way if not like how edit changed
sheet, close and reopen


: Hi
: AFAIK no chance as this macro disabled the Undo function
:
: --
: Regards
: Frank Kabel
: Frankfurt, Germany
:
:
: > I have put the following in excel sheet code
: > Private Sub Worksheet_SelectionChange(ByVal Target As Range)
: > If Range("B2") > 20 Then
: > Range("B2").Interior.ColorIndex = 4
: > Else: Range("B2").Interior.ColorIndex = 2
: > End If
: > End Sub
: > If I edit any cell, I cannot undo, since the undo button on the menu
: > bar becomes inactive.
: > How to overcome this?
: >
: >
: > ---
: > Message posted

 

[Dave Peterson]

You may want to look at Format|Conditional formatting.

It looks like it would be simpler in this case.

(no macro, no problem with undo)

 

[Bhuktar S]

Dear Layla,
No, the edited cell does not undo but goes to previous action (in m
case I had another book opend & when I clicked the Undo on thi
workbook, it opend the earlier workbook).

Dear Dave,
That was an example to see if someone can suggest a solution.
Yes, for the example I gave, conditional formatting will help.
But what if instead of cell B2 to format, cell B2 value to be 100, els
B2 value to be 0 etc.

Is there any other method or code or so to overcome this problem

 

[Dave Peterson]

That's one of the prices for the worksheet events.

John Walkenbach has described a way to do your own Undo:
http://j-walk.com/ss/excel/tips/tip23.htm