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?

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

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.

"Biff" wrote:

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

Using Conditional Formatting.

Select table1 range and FormatCFCondition1Formula is: =$A$1="a"

Format to yellow.

Condition2Formula 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("B1:D8").Cells.Locked = True
ElseIf Target.Value = "b" Then
Range("B9:D16").Cells.Locked = True
End If
ActiveSheet.Protect
End If
enditall:
Application.EnableEvents = True
End Sub


Gord Dibben Excel MVP

On Sat, 21 May 2005 11:59:01 -0700, "Using one table instead of another?"
<Using one table instead of wrote:

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?