Retrospective worksheet change event?

Hi all,

I've got a large pricing spreadsheet that is currently being updated by
several users. When I get it back I need to be able to see what changes have
been made and to check whether formulas are still intact (not pasted over).

I didn't apply any worksheet change event code before hand but I did create
a copy of the original data and put on a tab called 'Save' (this is hidden) -
so I was wondering if there was any way of comparing the two worksheets and
highlighting any changes using the Save copy as a baseline??

This is what I need to check (and maybe highlight with a background fill
colour):

- that there were NO changes in columns A-O
- if column P was changed, then column Q must be populated
- U, Z, AF, AL, AR, AT must have intact formula.
- if column AT is >10% or <-10% turn font RED. Red values must have comment
entered in column BN so highlight empty BN cells if AT is red.

so... as you can see I have quite a challenge ahead of me!!
Can someone please let me know if this is even possible??

Thank you in advance! :-)

> - that there were NO changes in columns A-O
> - U, Z, AF, AL, AR, AT must have intact formula.

Unlock cells you allow to be edited by users and protect the sheet to
prevent changes to non-locked cells!

> - if column P was changed, then column Q must be populated

Insert a locked copy of column P (you may as well hide it), say column AZ!
Apply conditional formatting on column Q:
Formula: and(P1<>AZ1,isblank(Q1)) and choose a color!


> - if column AT is >10% or <-10% turn font RED. Red values must have comment
> entered in column BN so highlight empty BN cells if AT is red.
Apply conditional formatting on both column AT and BN:
Formula: or(AT1>0.1,AT1<-0.1) and choose a color!

Regards,
Stefi

Hi Stefi,

Thanks for the quick reply but its a bit more complicated than that I think...

> Unlock cells you allow to be edited by users and protect the sheet to
> prevent changes to non-locked cells!

I can't protect columns like that by locking (apart from having already
issued the spreadsheet, hence the retrospective part of my posting!) as I
needed to set custom views - these didn't work once you protected the
columns/worksheet so I had to let it go with no protection in the hope I
could do some checks afterwards.

> Insert a locked copy of column P (you may as well hide it), say column AZ!
> Apply conditional formatting on column Q:
> Formula: and(P1<>AZ1,isblank(Q1)) and choose a color!

Thanks for this formula, I'd semi thought of that myself just not got my
head around it yet!!
Instead of inserting extra columns I've just set the formatting to reference
my Save sheet.

> Apply conditional formatting on both column AT and BN:
> Formula: or(AT1>0.1,AT1<-0.1) and choose a color!

I'm just going to go mad with conditional formatting I think!!! Cheers Stefi
:-)

Check out Tools|Track Changes.

Meltad wrote:
>Hi all,
>
>I've got a large pricing spreadsheet that is currently being updated by
>several users. When I get it back I need to be able to see what changes have
>been made and to check whether formulas are still intact (not pasted over).
>
>I didn't apply any worksheet change event code before hand but I did create
>a copy of the original data and put on a tab called 'Save' (this is hidden) -
>so I was wondering if there was any way of comparing the two worksheets and
>highlighting any changes using the Save copy as a baseline??
>
>This is what I need to check (and maybe highlight with a background fill
>colour):
>
>- that there were NO changes in columns A-O
>- if column P was changed, then column Q must be populated
>- U, Z, AF, AL, AR, AT must have intact formula.
>- if column AT is >10% or <-10% turn font RED. Red values must have comment
>entered in column BN so highlight empty BN cells if AT is red.
>
>so... as you can see I have quite a challenge ahead of me!!
>Can someone please let me know if this is even possible??
>
>Thank you in advance! :-)

--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...mming/200608/1

Thanks but the users have already started making changes so can I still track
the changes in this way? It would be perfect if i could...

Mel


"skatonni via OfficeKB.com" wrote:

> Check out Tools|Track Changes.
>
> Meltad wrote:
> >Hi all,
> >
> >I've got a large pricing spreadsheet that is currently being updated by
> >several users. When I get it back I need to be able to see what changes have
> >been made and to check whether formulas are still intact (not pasted over).
> >
> >I didn't apply any worksheet change event code before hand but I did create
> >a copy of the original data and put on a tab called 'Save' (this is hidden) -
> >so I was wondering if there was any way of comparing the two worksheets and
> >highlighting any changes using the Save copy as a baseline??
> >
> >This is what I need to check (and maybe highlight with a background fill
> >colour):
> >
> >- that there were NO changes in columns A-O
> >- if column P was changed, then column Q must be populated
> >- U, Z, AF, AL, AR, AT must have intact formula.
> >- if column AT is >10% or <-10% turn font RED. Red values must have comment
> >entered in column BN so highlight empty BN cells if AT is red.
> >
> >so... as you can see I have quite a challenge ahead of me!!
> >Can someone please let me know if this is even possible??
> >
> >Thank you in advance! :-)
>
> --
> Message posted via OfficeKB.com
> http://www.officekb.com/Uwe/Forums.a...mming/200608/1
>
>

Sorry read the thread too quickly. If you've already released the workbook
and you cannot find an answer, perhaps you can justify buying software.

http://www.officekb.com/Uwe/Forum.as...xcel-Workbooks


Meltad wrote:
>Thanks but the users have already started making changes so can I still track
>the changes in this way? It would be perfect if i could...
>
>Mel
>
>> Check out Tools|Track Changes.
>>
>[quoted text clipped - 22 lines]
>> >
>> >Thank you in advance! :-)

--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...mming/200608/1

One last kick at the can. Maybe you can use this code.

http://www.erlandsendata.no/english/...vbawscomparews

skatonni wrote:
>Sorry read the thread too quickly. If you've already released the workbook
>and you cannot find an answer, perhaps you can justify buying software.
>
>http://www.officekb.com/Uwe/Forum.as...xcel-Workbooks
>
>>Thanks but the users have already started making changes so can I still track
>>the changes in this way? It would be perfect if i could...
>[quoted text clipped - 6 lines]
>>> >
>>> >Thank you in advance! :-)

--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...mming/200608/1

Thanks for this, I like it!
Will probably use it alongside another macro my colleague is writing to
ensure proper validation in certain columns.


"skatonni via OfficeKB.com" wrote:

> One last kick at the can. Maybe you can use this code.
>
> http://www.erlandsendata.no/english/...vbawscomparews
>
> skatonni wrote:
> >Sorry read the thread too quickly. If you've already released the workbook
> >and you cannot find an answer, perhaps you can justify buying software.
> >
> >http://www.officekb.com/Uwe/Forum.as...xcel-Workbooks
> >
> >>Thanks but the users have already started making changes so can I still track
> >>the changes in this way? It would be perfect if i could...
> >[quoted text clipped - 6 lines]
> >>> >
> >>> >Thank you in advance! :-)
>
> --
> Message posted via OfficeKB.com
> http://www.officekb.com/Uwe/Forums.a...mming/200608/1
>
>