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

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...
--
HTH...

Jim Thomlinson


"mark" wrote:

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

But see

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

for some workarounds.

In article ,
Jim Thomlinson wrote:

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

okay, thanks. I see what you are saying.

Got it.

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...
--
HTH...

Jim Thomlinson


"JE McGimpsey" wrote:

But see

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

for some workarounds.

In article ,
Jim Thomlinson wrote:

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

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.

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

In article ,
Jim Thomlinson wrote:

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...

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.
--
HTH...

Jim Thomlinson


"JE McGimpsey" wrote:

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

In article ,
Jim Thomlinson wrote:

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...

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?

--
Regards,
Tom Ogilvy


"JE McGimpsey" wrote:

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

In article ,
Jim Thomlinson wrote:

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...

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.


In article ,
Tom Ogilvy wrote:

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?

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

--
Regards,
Tom Ogilvy


"JE McGimpsey" wrote:

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.


In article ,
Tom Ogilvy wrote:

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?

....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...
--
HTH...

Jim Thomlinson


"JE McGimpsey" wrote:

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.


In article ,
Tom Ogilvy wrote:

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?