I'm not aware of any formatting code which will do this for you.
Pete
> Excel 2003, sp2
>
[quoted text clipped - 16 lines]
> Regards
> PO
If you want to keep a value in those cells, you'll have to use a macro. In
fact, if you wanted to use a formula, you'd need to use some helper cells.
But how would you know what values are original? If I change a value (from 1 to
10, say) and the value in B1 is 110%, is my original value 1, 10, 1.10, or 11.0?
If you can decide, maybe you could put those original (whatever that means) on a
separate (hidden) sheet.
Without caring about the original values, you could use a worksheet event that
does the work:
Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
Dim myRng1 As Range
Dim myRng2 As Range
Set myRng1 = Me.Range("A3:B10")
Set myRng2 = Me.Range("B1")
With Target
If .Cells.Count > 1 Then
Exit Sub
End If
On Error GoTo ErrHandler:
If Not (Intersect(myRng1, .Cells) Is Nothing) Then
'in A3:B10
If IsNumeric(myRng2.Value) Then
If IsNumeric(.Value) Then
If IsEmpty(myRng2.Value) = False Then
Application.EnableEvents = False
.Value = .Value * myRng2.Value
End If
End If
End If
ElseIf Not (Intersect(myRng2, .Cells) Is Nothing) Then
'in B1
If IsNumeric(.Value) Then
If IsEmpty(.Value) = False Then
Application.EnableEvents = False
.Copy
myRng1.PasteSpecial Paste:=xlPasteValues, _
operation:=xlPasteSpecialOperationMultiply
Application.CutCopyMode = False
.Select
End If
End If
End If
End With
ErrHandler:
Application.EnableEvents = True
End Sub
> Excel 2003, sp2
>
[quoted text clipped - 16 lines]
> Regards
> PO

Signature
Dave Peterson