One cell changes another or vice versa + change value of linked cell does not update

**gemmott** · 08-11-2014, 09:42 PM

I am doing a quick margin or selling price calculator based on enters costs.
I have a code that allows updates/calculates of either the margin or selling price and vice versa ("B6" and "B7"). This works fine and please see code below.

The problem is if I change one of the costs I want it to automatically update the selling price instead of manually pressing "F2" and "Enter" in the margin cell ("B6"). Any ideas of what I need to do to achieve this?

MarginTest.xlsm - I have attached the sheet, any help would be appreciated.

Capture.JPG

Private Sub Worksheet_Change(ByVal Target As Range)
   If Target.Count > 1 Then Exit Sub
    If Target.Column < 2 Or Target.Column > 2 Then Exit Sub
    If Target.Row < 6 Or Target.Row > 7 Then Exit Sub
    Application.EnableEvents = False
    Select Case Target.Row
    Case Is = 6
        If Target.Offset(-2, 0) = "" Then
            Application.EnableEvents = True
            Exit Sub
        Else: Target.Offset(1, 0) = Target.Offset(-2, 0) / (1 - Target)
        End If
    Case Is = 7
        If Target.Offset(-3, 0) = "" Then
            Application.EnableEvents = True
            Exit Sub
        Else: Target.Offset(-1, 0) = (Target - Target.Offset(-3, 0)) / Target
        End If
    End Select
    Application.EnableEvents = True
    
End Sub

**jaslake** · 08-12-2014, 03:07 PM

Hi gemmott

Try this

Private Sub Worksheet_Change(ByVal Target As Range)
  If Target.Count > 1 Then Exit Sub
  If Not Target.Column = 2 Then Exit Sub
  Application.EnableEvents = False
  Select Case Target.Row

  Case Is = 1, 2, 3
    Range("B6").Activate
    Application.SendKeys Keys:="{F2}"
    Application.Wait (Now() + TimeValue("00:00:01"))
    Application.SendKeys "{ENTER}"
  Case Is = 6
    If Target.Offset(-2, 0) = "" Then
      Application.EnableEvents = True
      Exit Sub
    Else: Target.Offset(1, 0) = Target.Offset(-2, 0) / (1 - Target)
    End If
  Case Is = 7
    If Target.Offset(-3, 0) = "" Then
      Application.EnableEvents = True
      Exit Sub
    Else: Target.Offset(-1, 0) = (Target - Target.Offset(-3, 0)) / Target
    End If
  End Select
  Application.EnableEvents = True
End Sub