erase on insert

[quando]

I have a block of cells on a single line. Only 1 cell can have a value across all cells in the block. I wish to be able to insert a new value into an empty cell and automatically erase the former value from a different cell.

Is there a way to do this?

I am using Excel 2003.

Thanks,

 

[lenze]

You are refereing to mutually exclusive cells. Several ways, depending on your needs. Specify the Columns of interest and what you mean by Blocks!!
1) Columns "A:F" ?
2) One Row, Several Rows or Each Row Independent of other Rows?

lenze

 

[MickG]

Hi, You may or may not mean this !!!:-

Private [COLOR=navy]Sub[/COLOR] Worksheet_Change(ByVal Target [COLOR=navy]As[/COLOR] Range)
Dim Temp 
Application.EnableEvents = False
    [COLOR=navy]If[/COLOR] Target.Count = 1 [COLOR=navy]Then[/COLOR]
        Temp = Target
        Target.EntireRow.ClearContents
        Target = Temp
    [COLOR=navy]End[/COLOR] If
Application.EnableEvents = True
[COLOR=navy]End[/COLOR] [COLOR=navy]Sub[/COLOR]

Regards Mick

 

[quando]

lenze -
Let's say in row 1, A:F is my "block", by which I mean I am restricting my attention to these cells alone. Is there a better term to use?

Thanks,

 

[quando]

one more thing that could be helpful - there's nothing else of interest on this row.

 

[lenze]

Like this??

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Count > 1 Then Exit Sub
If Intersect(Target, Range("$A$1:$F$1")) Is Nothing Then Exit Sub
Application.EnableEvents = False
myval = Target
Range("$A$1:$F$1").ClearContents
Target = myval
Application.EnableEvents = True
End Sub

If you want it for A:F on all Rows, use something like

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Count > 1 Then Exit Sub
If Target.Column > 6 Then Exit Sub
Application.EnableEvents = False
myval = Target
Cells(Target.Row, "A").Resize(1, 6).ClearContents
Target = myval
Application.EnableEvents = True
End Sub

lenze