Mike,

Your help is again priceless to me. Following your advices I've
modified the code as follow, now I could also modify other cells with
the values I need. There is no need of an error control, those colums
are a fixed output database and they are always number... Forgive my
"baboon" coding but it's working :)

....What is now beyond my grasp is to add a conditional cell formatting
(color, bold whatever) of the changed cell to have a quick check
against with the original output.

Any other advice will be more than welcomed and appreciated....

Many thanks,

Tony



Option Explicit

Dim lastrow As Variant
Dim Myrange As Variant
Dim Cell As Variant
Dim Val1 As Long


Sub stance1()

Val1 = Sheet2.Range("B1").Value

On Error Resume Next

lastrow = Range("AY65536").End(xlUp).Row
Set Myrange = Range("AY1:AY" & lastrow)
For Each Cell In Myrange
If Cell.Value = Worksheets("Sheet2").Range("A1").Value Then
Cell.Offset(0, -7).Value = Cell.Offset(0, -7).Value / 2

End If

If Cell.Value = Worksheets("Sheet2").Range("A1").Value Then
Cell.Offset(0, -34).Value = Val1
End If

Next

End Sub