I am not sure if this does what you want, but it does make
things interesting. Place the code in the module behind the sheet...
'----------------------------
Private Sub Worksheet_Change(ByVal Target As Range)
'Jim Cone - San Francisco, USA - December 2006
'Only one cell in the row intersecting Columns D & E can have a value.
On Error GoTo ErrHandler
Dim rngIntersect As Excel.Range
Dim blnCol As Boolean
Set rngIntersect = Application.Intersect(Target, Me.Range("D12:E1000"))
If Not rngIntersect Is Nothing Then
Application.EnableEvents = False
If rngIntersect.Count = 1 Then
'Col D returns 0, Col E returns -1
blnCol = (rngIntersect.Column = 5)
If Len(rngIntersect) Then
'rngIntersect(1, 2) or rngIntersect(1, 0)
rngIntersect(1, 2 + (2 * CLng(blnCol))).ClearContents
End If
Else
'More than one cell changed in Col D or E so undo changes...
Application.Undo
MsgBox "Change only one cell at a time in columns E and F ", _
vbExclamation, "Hope This Makes Sense"
End If
End If
ErrHandler:
Application.EnableEvents = True
Set rngIntersect = Nothing
End Sub
---------------
Jim Cone
San Francisco, USA
http://www.realezsites.com/bus/primitivesoftware




wrote in message
Hello,
I am an intermediate user of excel, however my knowledge of VBA is a
little rusty. I am currently stumped on this problem for a worksheet I
working on:

Worksheet is "RateSheet" and already unlocks and locks certain ranges
on worksheet.change event
Range "A" is D12:D1000
Range "B" is E12:E1000
Validation for both ranges is positive currency values

I want to compare a cell from A to the corresponding cell in B and
allow information to only be entered in one OR the other cell.
Therefore if the user enters a number in A, the user will not be able
to enter any values in B, and ultimately B will be greyed out as well.
The same goes for B, a value entered will have the same lockout results
in A. If A had information and B was locked out, deleting or clearing
A would unlock B.
Hope this makes sense... I've searched but have not found exactly what
I am looking for...