Cell Value

D

Dan

Here's what I want to do I have(for simplicity)rows 1-5
and col. A-E. For input reasons in A1-5 the value is
either 1 or its empty AND if a 1 is entered in one cell
then entered in another it removes it from the prior.

Basically within the range blank or 1 and in one cell

How do I go about this?
 
F

Frank Kabel

Hi
this is only possible with a event procedure. Put the following in your
worksheet module (not in a standard module)

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Cells.Count > 1 Then Exit Sub
If Intersect(Target, Me.Range("A1:E5")) Is Nothing Then Exit Sub
On Error GoTo CleanUp
Application.EnableEvents = False
Application.ScreenUpdating = False
With Target
If .Value = 1 Then
Range(Cells(.row, 1), Cells(.row, 5)).ClearContents
.Value = 1
End If
End With
CleanUp:
Application.EnableEvents = True
Application.ScreenUpdating = True
End Sub
 
Top