Formula or code

[noelmus]

Hi,
Currently in worksheet (CHIT) I have E22=E20-E21 and the same H22=H20-H21, so I'm going to focus on E22=E20-E21.
I need that I can also update E22 so that E20=E21+E22. So in few words, I can update E20 to get an answer in E22 or update E22 to get an answer in E20. Just to remind you that even for H20,H21 and H22, I need the same thing.
I don't know if its possible to work with a formula or code. In case it works with a code I already have these:


In workbook:


Private Sub Workbook_SheetActivate(ByVal Sh As Object)
Dim ws As Worksheet


Set ws = Worksheets("CHIT")
If Sh.Name = ws.Name Then
ws.Range("C11") = Application.UserName
End If


Set ws = Nothing
End Sub
Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
Sheets("CHIT").Range("J9,J12,C14,E28,E29").Interior.ColorIndex = 6
End Sub


In worksheet:


Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("J9,J12,C14,E28,E29")) Is Nothing Then
If Target.Count > 1 Then Exit Sub
If Target.Value = "" Then
Target.Interior.ColorIndex = 6
Else
Target.Interior.ColorIndex = xlNone
End If
End If
End Sub

Thanks in advance

 

[James006]

Hi,

You will need an Even macro ...

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Count > 1 Then Exit Sub
If Intersect(Target, Range("E20,E22,H20,H22")) Is Nothing Then Exit Sub
Application.EnableEvents = False
    If Target.Row = 20 Then
        Target.Offset(2, 0) = Target - Target.Offset(1, 0)
    Else
        Target.Offset(-2, 0) = Target + Target.Offset(-1, 0)
    End If
Application.EnableEvents = True
End Sub

Hope this will help

 

[noelmus]

Hi James006
Better if you was James007 cause the code works perfect.

Thanks a lot

 

[James006]

Glad the macro is helping you out ...

006 is Before tax ..

So if you were to add 16.66 % Tax ... you will probably reach 7 ...