Changing cell formula is other cell changes (Excel 2000)

[bluerayz]

Hi Lounge,
I am looking for help on an issue that I have with being able to override a cell that had a formula in it and back calculate by adding a formula to a cell that use to reference a cell,

In my example,

I am calculating the amount of cement needed for a job,

There is 2 ways to calculate it,
The slurry wet amount measured in m

[HansV]

I think you'd have to use the Worksheet_Change event:

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Range("G75"), Target) Is Nothing Then
Application.EnableEvents = False
Range("I75").Formula = "=G75/D75"
Application.EnableEvents = True
ElseIf Not Intersect(Range("I75"), Target) Is Nothing Then
Application.EnableEvents = False
Range("G75").Formula = "=I75*D75"
Application.EnableEvents = True
End If
End Sub

It is not clear to me, however, if and when you would want to restore the original formula =I58 in G75.

[bluerayz]

Thanks Hans, Quick reply,

I dont need to cange the G75 back to =I58 If a change is made then there is no going back.

It work great,

On question, If I already have worksheet change code present behind the worksheet, do i simply just add the code you provided along with the other code in the same sub?

I am not sure how to name the sub differently then Private Sub Worksheet_Change(ByVal Target As Range) so I am assuming i just add it to the same sub. Let me know if I am incorrect.
Thanks again,

[sdckapr]

You can add multiple code, but the comparisons and intersects can make the code sluggish when you edit the cells, since the event is called everytime that a cell is changed.

Steve

[bluerayz]

Hi Steve,

Any way to get around this by break it up?

[HansV]

I don't think that'll make a difference. On most recent PCs, you won't notice the overhead, by the way - you'd have to write quite a bit of code before it would really slow down Excel. Still, you should only include code you really need.