Using one table instead of another?

  • Thread starter Using one table instead of another?
  • Start date
U

Using one table instead of another?

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

x5bulldog

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

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("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
 
Top