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.


--

Regards,
Nigel




"LRay67" wrote in message
...
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

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
--
HTH...

Jim Thomlinson


"LRay67" wrote:

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

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" wrote:

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

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??

"Jim Thomlinson" wrote:

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
--
HTH...

Jim Thomlinson


"LRay67" wrote:

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

Nice, very useful!

"Jim Thomlinson" wrote:

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
--
HTH...

Jim Thomlinson


"LRay67" wrote:

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