Worksheet_Change multiple ranges and different formulas
Hi everyone
I am building a new file for my company and I find myself in need of multiple Worksheet_Change one one of my sheets.
The file consist of multiple months, and for each month we are entering a value and we would like it to calculate a value based imputed in the previous cell. And vica versa.
Some content: We recalculate always from $ to our cent/Kg or maybe we have a target Cent/kg we then calculates to full $-value.
Example of my sheet.
KG, TONNES |
92,349 |
|
|
$ 1,000 |
Cent/KG |
800,000 |
866 |
78,005 |
84 |
We have the above for full 12 months.
My code now for the first column to create a formula that is $/KG*100.
Which works when we input a value in our $-column. But we also need to opportunity to directly write the Cent/KG, and then automatically calculate back to $.
I know how to write to two codes seperately - but my issue is to combine multiple "Worksheet_Change"..
I tried to use "If Target.Column = xx Then" "Else" but with no luck.
Code:
Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("P24:P31", "P33:P34")) Is Nothing Then
Target.Offset(0, 0).Activate
Dim acc_mth1 As Range
Set acc_mth1 = ActiveCell
Application.ScreenUpdating = False
If IsNumeric(acc_mth1.Value) Then
acc_mth1.Offset(0, 1).Formula = "=" & acc_mth1.Address & "/$P$19*100"
End If
If acc_mth1.Value = "" Then
acc_mth1.Offset(0, 1).ClearContents
End If
End If
End Sub
Any tips for how to solve this best?
Best regards,
Rasmus