changing cell value

[Kea]

Does anyone know how to have the value of a cell change
automatically using a function or formula? I am creating a
spreadsheet for a contest in which employees will enter
the number of points earned each week. However, if they
enter a zero I want the value of the cell to automatically
change to -20 (if they earn zero points in a week then
they are to have 20 points deducted from their team
score). I can not seem to find any way to accomplish this
without excel telling me I am making a circular reference.
Help!

 

[Don Guillett]

right click on sheet tab>view code>insert this>save
As written it will ONLY work in col B. Change to suit.

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column <> 2 Then Exit Sub
If Target = 0 Then Target = -20
End Sub