I found a great answer to a previous question provided by @angelofdev to a problem of exchanging the user entered data with the result of a formula,HERE. The code works perfectly, but I am a little stuck. I tried commenting on that thread, but thought this might be better?
I need to apply this to a range of cells, e.g.
(D1:D20), but if I enter this into his code, it doesn't work and I'm not sure where I'm going wrong?
The code is:
Private Sub Worksheet_Change(ByVal Target As Range) ' The below If statement uses Intersect to check, ' if the cell being changed is NOT cell D1 it gets ignored. If Not Intersect(Target, Target.Worksheet.Range("D1")) Is Nothing Then Application.EnableEvents = False 'Disables events to prevent endless loop On Error GoTo Finalise 'Re-enable events ' The below code gets the value from cell D1, ' and stores the value to the inputVal variable. inputVal = Range("D1").Value ' The below code does your calculation, ' and stores the value to the newValue variable. newValue = inputVal * 0.58 'Changes the value in cell D1 to the value of the newValue variable. Range("D1").Value = newValue End If Finalise: Application.EnableEvents = True End Sub