Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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! :-) |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Retrospective worksheet change event?
- 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(AT10.1,AT1<-0.1) and choose a color! Regards, Stefi |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Retrospective worksheet change event?
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(AT10.1,AT1<-0.1) and choose a color! I'm just going to go mad with conditional formatting I think!!! Cheers Stefi :-) |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Retrospective worksheet change event?
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Retrospective worksheet change event?
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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Retrospective worksheet change event?
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 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Retrospective worksheet change event?
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 |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Retrospective worksheet change event?
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Cell value change to trigger macro (worksheet change event?) | Excel Programming | |||
Worksheet Change event | Excel Programming | |||
Change Cell from Validated List Not Firing Worksheet Change Event | Excel Programming | |||
help with worksheet change event | Excel Programming |