Can't do that with a formula, need VBA.

Private Sub Worksheet_Change(ByVal Target As Range)

On Error GoTo ws_exit:
Application.EnableEvents = False
If Not Intersect(Target, Me.Range("A1:A10")) Is Nothing Then
With Target
If .Value < 140.5 Then
.Value = 85.5
.Offset(0, 1).Value = 0.1496
.Offset(0, 2).Value = 100.46
End If
End With
End If

ws_exit:
Application.EnableEvents = True
End Sub

'This is worksheet event code, which means that it needs to be
'placed in the appropriate worksheet code module, not a standard
'code module. To do this, right-click on the sheet tab, select
'the View Code option from the menu, and paste the code in.


I also think you calculations are wrong. By mine, with vat on 85.5 gives
98.29.

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"Ernest Lai" wrote in message
...
Hi all,

I have a table of items with "value" "vat" and "total" Columns. I would
like to write a formulae to say

When value is less than 140.5 then change value to 85.5, vat to 14.96,
total to 100.46 Else no change.

so im guessings its something like:
X=1
If AX<140.5 then AX=85.5, BX=14.96, CX=100.46
Else AX=AX,BX=BX,CX=CX
Increment x

Or i could just copy and paste the If Then statment down all the columns
and
forget the increment. Hopefully you will see what im trying to do.

Thanks in advance, any help is much appretiated as always.

Ernest Lai