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?