Using one table instead of another?

[Guest]

Say I created two tablesin Microsoft Excel. In a cell above the two tables i
insert a value. To use table 1 you insert the letter (a) or to use table 2
you insert (b). How can I do this?

when i insert the letter (a) I want table 1 to be highlighted yellow and
table 2 to be highlighted red. Also i want table 2 to be locked when i insert
the letter (a). The same goes for the letter (b) but vice versa. How do I do
this?

 

[Biff]

Hi!

What do you mean by:

Also i want table 2 to be locked when i insert the letter (a). The same
goes for the letter (b) but vice versa.

Biff

"Using one table instead of another?" <Using one table instead of
[email protected]> wrote in message

 

[Guest]

When I want to use table 1 i insert the letter (a). At the same time i want
table 2 to be locked. Meaning that no input values can be inserted into table
2 while table 1 is being modified.

I want to do the same if i am using table 2, where table 1 cannot be
modified.

 

[Gord Dibben]

Using Conditional Formatting.

Select table1 range and Format>CF>Condition1>Formula is: =$A$1="a"

Format to yellow.

Condition2>Formula is: =$A$1="b"

Format to red.

Do same for table2.

The "locking" of the tables must be done through VBA code.

One method........

Private Sub Worksheet_Change(ByVal Target As Range)
On Error GoTo enditall
Application.EnableEvents = False
If Target.Address = "$A$1" Then
ActiveSheet.Unprotect
ActiveSheet.Cells.Locked = False
If Target.Value = "a" Then
Range("B18").Cells.Locked = True
ElseIf Target.Value = "b" Then
Range("B916").Cells.Locked = True
End If
ActiveSheet.Protect
End If
enditall:
Application.EnableEvents = True
End Sub


Gord Dibben Excel MVP