cell change event, update values

[ram88]

Hi,

Hope you could help me with this. This is an "amount estimate" table. The computation centers on the Amt column. There will be a set amount in this column, e.g. 400. Then two columns on the left, to adjust values, one to increase (+) and the other to decrease (-) value in the Amt column. e.g. a 100 in increase(+)column and a 200 in the decrease (-) column, makes the Amt column to 300.

On the right of the Amt column is another set of two columns, this time its percent increase (+%) and the other column is percent decrease (-%). These two columns will represent the % expression of the increase (+) column and the decrease (+) column, i.e. 25% for the percent increase and 50% for the percent decrease.

Now, the amount initially entered should adjust based on the entries in either the percent or values columns. Example, this will be done by inserting a value in either the increase or decrease column. When that value is added it needs the value to automatically be added to Amt column and then show the % increase or % decrease. It should also work the opther way. If a value is entered at the increase or decrease using the % columns, they have to just put a value in the +% or -% column and it will automatically show the value that it has increased or decreased the Amt column.

Here's what I have done, which I haven't tested in totality:

'If E5:E7 value is changed via input and ENTER, dialogue box to input entered value in corresponding K5:K7

 Private Sub Worksheet_Change(ByVal Target As Range)
   If Not Application.Intersect(Target, Me.Range("F5:G7")) Is Nothing Then
        ' At least one cell of Target is within the range F5:G7 
        ' Carry out some action. 

Run ValuesMacro()

    Else
        ' No cell of Target in in the range F5:G7. Get Out, test second

   If Not Application.Intersect(Target, Me.Range("C5:D7")) Is Nothing Then
        ' At least one cell of Target is within the range C5:D7 
        ' Carry out some action. 
Run PercentMacro()



    Exit Sub





Sub PercentMacro()

    Range("C5").Select
    ActiveCell.FormulaR1C1 = "=RC[3]*RC[8]"
    Range("D5").Select
    ActiveCell.FormulaR1C1 = "=RC[7]*RC[3]"
    Range("E5").Select
    ActiveCell.FormulaR1C1 = "=(1+(RC[1]+(-1*RC[2])))*RC[6]"
    Range("C5:E5").Select
    Selection.Copy
    Range("C5:C7").Select
    ActiveSheet.Paste
    Range("C5").Select
    Application.CutCopyMode = False

End Sub



Sub Values Macro()

    Range("E5").Select
    ActiveCell.FormulaR1C1 = "=RC[6]+RC[-2]-RC[-1]"
    Range("F5").Select
    ActiveCell.FormulaR1C1 = "=(RC[5]+RC[-3])/RC[5]-1"
    Range("G5").Select
    ActiveCell.FormulaR1C1 = "=1-(RC[4]+RC[-3])/RC[4]"
    Range("E5:G5").Select
    Selection.Copy
    Range("E5:E7").Select
    ActiveSheet.Paste
    Range("E5").Select
    Application.CutCopyMode = False

End Sub