Using Worksheet_Change Event

Hi!



I have a live connection to my database in my workbook, so that every time data changes the cell value changes.



Now I am trying to use Worksheet_Change event to keep on track of the changes. So, every time a price changes for a product, the event would copy date, buyer name, product type, old price and new price



Please find my sample workbook attached.



Columns 1, 2 and 3 track the changes in prices: if a price has changed from last months price the value is "Change" and otherwise "OK"



Old values are: Range("Data[[#All],[Apple old]:[Banana old]]")

New values with live update are in Range("Data[[#All],[Apple]:[Banana]]")



I have a few issues that I am not able to solve. First of all, it seems that if the value in Range("Data[[#All],[1]:[3]]") changes it doesn't trigger the event because the change come through formula. If I just input a value in that range the event is triggered.. so is there a possibility to trigger the event if a cell contains a formula..?



Second problem is that I don't know how to copy the buyer name or the product name.



At the moment I am just using rngChanged.Offset(0, i) and it is working for prices because they are always -3 or 3 columns from changed cell. But how can I define that if a value from Range("Data[1]") is changed offset would be rngChanged.Offset(0, -4) and if from Range("Data[2]") it would be rngChanged.Offset(0, -5). The product name is in a header, so if Range("Data[1]") is changed it should be a header for a cell rngChanged.Offset(0, -3)



I know its a lot of issues here. any ideas are appreciated..thanks!



-Jack

Please Login or Register  to view this content.

[/CODE]

sorry. My brain is not working
I do not understand why do you need a event code at all)worksheet_change event code). the formulas are only in columns G to I .
suppose you change D3 to 6 then the value in G3 becomes "change". Of course a message box crops up and it is due to event code. If event code is not there there will not be any message box but G3 will be "change".

either this or I have not understood you.

your second question

that I don't know how to copy the buyer name or the product name.

where do you want buyer name and product name
I presume in sheet "analysis"

you can copy these things from sheet "data"
the date you copy from N2 and pastespecial and values in column B of "analysis" otherwise everytime it will give only today's date
for which a macro will be useful