Couple considerations:

1) A cell can have either a constant or a formula, not both.
2) A formula can't change a value in another cell.

If your vat column has something like the formula

Bx: =ROUND(Ax * 17.5%, 2)

and you total column has something like:

Cx: =Ax + Bx

then there's no need to change B & C when A is changed.

This macro will change any value in column A that is less than 140.5 to
85.5:

Public Sub LessThan140()
Dim rCell As Range
For Each rCell In Range("A2:A" & _
Range("A" & Rows.Count).End(xlUp).Row)
With rCell
If IsNumeric(.Value) Then _
If .Value < 140.5 Then _
.Value = 85.5
End With
Next rCell
End Sub

Alternatively, if you need columns B & C to be constants, you can use

Public Sub LessThan140()
Dim rCell As Range
For Each rCell In Range("A2:A" & _
Range("A" & Rows.Count).End(xlUp).Row)
With rCell
If IsNumeric(.Value) Then
If .Value < 140.5 Then
.Value = 85.5
.Offset(0, 1).Value = 14.96
.Offset(0, 2).Value = 100.46
End If
End If
End With
Next rCell
End Sub

If you're not familiar with macros, see

http://www.mvps.org/dmcritchie/excel/getstarted.htm


In article ,
"Ernest Lai" wrote:

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