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

  • Thread starter Thread starter LRay67
  • Start date Start date
L

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
 
Use both "Data Validation" to control the entry rule and "Conditional
Formatting" to control the format.
 
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
 
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
 
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??
 
Nice, very useful!

Jim Thomlinson said:
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
Click to expand...
 
Back
Top