Worksheet Change event code, but retain Undo?

[Guest]

Hi.

A team here wanted some formatting, dependent upon 4 other cells, in an OR
formula.

if =or(f33<f39,f34<f40) is true, they want cell f1 to have a red background.
If it's false, it has the default blue background.

Since conditional formatting is only dependent upon the value of the cell to
which the cell is being applied, the way I saw to do this was with some
custom code.

I put some code into the worksheet_change event which does the formatting
fine.

But, now the Undo button is not available... running the custom code takes
away the possibility of Undo. (no particular surprise).

Is there a way to have both?

Thanks,
Mark

 

[Guest]

Conditional formatting can be dependant on whatever you want it to be. Change
from cell value to Formula and go to town with it...
=or(f33<f39,f34<f40)
Is a perfectly valid conditional format in any cell you want to put it...

To answer your other question code execution wipes out the undo and there is
no way around it...

 

[JE McGimpsey]

But see

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

for some workarounds.

 

[Guest]

okay, thanks. I see what you are saying.

Got it.

 

[Guest]

That undoes the subroutine but what about the changes that the user made
prior to running the code. That is lost forever isn't it? I was asuming that
that is what the user meant by undo...

 

[Guest]

That undoes the subroutine but what about the changes that the user made

prior to running the code. That is lost forever isn't it? I was asuming that
that is what the user meant by undo...

It was.

The formula conditional formatting that you mentioned solves the specific
problem, but general info is good, too.

Thanks.

 

[JE McGimpsey]

Yes, unless your code also saves a "stack" of previous changes, too
(it's a PITA, but it can be done).

 

[Guest]

I have never bothered trying to save the undo stack. For one project I did
maintain a listing af all of the changes made by the user and gave them some
ability to roll back their changes but it was kinda crude and a whole pile of
work.

 

[Guest]

I assume you are not saying you can access the Undo history programmatically
and save it before the macro wipes it out? If you are saying you can grab
the Excel built in undo history and later restore it or otherwise interpret
it and "undo", what objects or API is involved?

 

[JE McGimpsey]

No, I'm saying you have to generate your own "stack" via
Workbook_SheetChange/SelectionChange/etc and save it somewhere.

The two times I've done it, it's been a real kludge and the client has
paid dearly for the privilege. They were happy, though.

 

[Guest]

Thanks for the response and glad to see it was profitable <g>

 

[Guest]

....it's been a real kludge and the client has paid dearly for the privilege

So it was not just me hacking something together for hours on end... oddly
enough I feel better. Not much better... but better...