Using Worksheet_Change Event

[John99]

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

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim rngChanged As Range, Changeables As Range, Cell As Range, Today As Range
    
    Set Changeables = Range("Data[[#All],[1]:[3]]")
    Set rngChanged = Intersect(Target, Changeables)
       
   If rngChanged Is Nothing Then
        MsgBox "No changes"
       Exit Sub
   ElseIf rngChanged.Address <> Target.Address Then
        MsgBox "No changes2"
       Exit Sub
   Else
       Range("Today").Copy
       
       Sheets("Analysis").Activate
       
       For Each Cell In Sheets("Analysis").Range("Analyzer[Date]")
    
        If Cell.Value = "" Then
            Cell.Select
            
            'Paste date
            Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
            xlNone, SkipBlanks:=False, Transpose:=False
        
            Sheets("Data").Activate
            rngChanged.Offset(0, -4).Copy
            Sheets("Analysis").Activate
            Cell.Offset(0, 1).Select
            Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
            :=False, Transpose:=False
        
            'copy  old price
            Sheets("Data").Activate
            rngChanged.Offset(0, 3).Copy
            Sheets("Analysis").Activate
            Cell.Offset(0, 2).Select
            Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
            :=False, Transpose:=False
        
            'copy  new price
            Sheets("Data").Activate
            rngChanged.Offset(0, -3).Copy
            Sheets("Analysis").Activate
            Cell.Offset(0, 3).Select
            Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
            :=False, Transpose:=False
        
        Exit For
        End If
       Next
   End If

 

 

End Sub

[/CODE]