Capture cell value before pressing enter?

[JazzSP8]

Hey All

I've a worksheet that I could use some help with.

I've been asked to create one that will take the changes to a price and apply those changes across various price breaks.

What has been asked is that, if a price in Col P is changed, the same percentage price increase is applied to various other prices in corresponding columns.

So, keeping it easy - Price in in Col P is £1.00, and it's changed to £1.10 - The prices in Col R, T, V and X all increase by 10% as well.

I'd originally planned to use a formula based on the Price in Col P, however it maybe possible that the price in Col R, T, V or X needs to be overwritten and thus losing the formula.

Is there a way I can get Excel to recognise the "before" value of the cell?

So when enter is pressed I can use a Worksheet Change event to take the price before and after cell value, and then add the % price increase to the other cells?

Thanks in advance for any help that can be provided

 

[wideboydixon]

You'd need to "remember" the cell value when the selection changes. Right click the sheet name, select "View Code" and paste the following:

' The last value in the cell before the update
Private lastValue As Variant
Private Sub Worksheet_Change(ByVal Target As Range)

Dim percentageChange As Double
Dim thisCol As Long

' Only works on single cell changes
If Target.Count = 1 Then
    ' Only consider columns P, R, T, V and X
    If Target.Column >= 16 And Target.Column <= 24 And Target.Column Mod 2 = 0 Then
        ' Calculate the percentage change from the original value
        percentageChange = Target.Value / lastValue
        
        ' Turn off events for now as we're going to update some other cells
        Application.EnableEvents = False
        
        ' Reflect changes into P, R, T, V and X
        For thisCol = 16 To 24 Step 2
            ' Ignore the column that got changed
            If thisCol <> Target.Column Then
                ' Adjust the value in this column using the same percentage change
                Cells(Target.Row, thisCol).Value = Cells(Target.Row, thisCol).Value * percentageChange
            End If
        Next thisCol
        
        ' Re-enable events
        Application.EnableEvents = True
    End If
End If

End Sub
Private Sub Worksheet_SelectionChange(ByVal Target As Range)

' Remember the last value in this cell
If Target.Count = 1 Then lastValue = Target.Value

End Sub

That will capture changes to cells in column P, R, T, V or X and apply the same percentage change to the other cells on that row.

WBD

 

[JazzSP8]

Excellent - That looks like something I can work with.

Thanks for taking the time to do the full code, great example to go at