User enters data in one cell, want to block entry into another cel

[LRay67]

I have this spreadsheet that if the user enters data in one cell I do not
want them to enter data into another cell. How can I accomplish this? Also
is there a way to put the cell that I don't want them to enter data into be
grayed out? Any help would be appreciated. Thanks

 

[Nigel]

Use both "Data Validation" to control the entry rule and "Conditional
Formatting" to control the format.

 

[Jim Thomlinson]

There are 2 parts to this. First blocking entry. This can be done with custom
validation. If I want to block cell A1 from entry if B1 has a value I would

Select Cell A1
Select Data | Validation | Custom
Add this formula
=B1=""
Select Ok
Now when B1 has a value A1 can not accept a value.
Do the Same for B1 with the formula =A1=""

To give a grey appearance you can use conditional formatting.
Select A1
Select Format | Conditional Formatting... | Formula is
=B1<>""
Select Format | Pattern | Grey colour.
Repeate for Cell B1

 

[Sam Wilson]

Right-click the tab and view code - paste this in (changeing A1 & B1 to the
cells you want.

Private Sub Worksheet_Change(ByVal Target As Range)

Application.EnableEvents = False
Dim ws As Worksheet
ActiveSheet.Unprotect

On Error GoTo ErrorCatcher

If Not Intersect(Target, Range("A1")) Is Nothing Then
With Range("B1")
If IsEmpty(Range("A1")) Then
.Interior.ColorIndex = -4142
.Locked = False
Else
.Interior.ColorIndex = 15
.Locked = True
End If
End With
End If

ErrorCatcher:
Application.EnableEvents = True
ActiveSheet.Protect

End Sub

 

[LRay67]

Jim, thanks that does work, except one of the cell's I want to block has a
drop down list for them to select from. In this case I can't select the
custom way without losing my drop-down selections. Anyway around this
situation??

 

[Charlie]

Nice, very useful!

There are 2 parts to this. First blocking entry. This can be done with custom
validation. If I want to block cell A1 from entry if B1 has a value I would

Select Cell A1
Select Data | Validation | Custom
Add this formula
=B1=""
Select Ok
Now when B1 has a value A1 can not accept a value.
Do the Same for B1 with the formula =A1=""

To give a grey appearance you can use conditional formatting.
Select A1
Select Format | Conditional Formatting... | Formula is
=B1<>""
Select Format | Pattern | Grey colour.
Repeate for Cell B1