You have to tell VBA what the ranges are somehow. If not by name, then by address. Then, it's easy to fire off with the Worksheet Calculate or Worksheet Change event.
If the addresses are static, in the VBA editor, paste the code below into the worksheet code module (not a regular module). Every time the worksheet calculates, it will evaluate Range1 and Lock/Unlock Range2. Replace Range1Address and Range2Address accordingly. You could also use named ranges.
VBA Code:
Private Sub Worksheet_Calculate() 'you could use Change here as well.
Range("Range2Address").Locked = (Application.WorksheetFunction.CountBlank(Range("Range1Address"))) = Range("Range1Address").Rows.Count
End Sub
If you want to automatically protect and unprotect , then add lines to the Calculate macro. This will unprotect/protect is the sheet starts as protect and do nothing if the sheet starts as unprotected. You can do the same with the calculate macro below.
VBA Code:
Private Sub Worksheet_Calculate() 'you could use Change here as well.
tmp = Me.ProtectContents: Me.Unprotect
Range("Range2Address").Locked = (Application.WorksheetFunction.CountBlank(Range("Range1Address"))) = Range("Range1Address").Rows.Count
If tmp Then Me.Protect
End Sub
If you want to make it easy to change, you could use a volatile custom function (it evaluates every time the worksheet is calculated) to set some public range variables. The functon returns TRUE if locked and FALSE if not. Then, the worksheet calculate event Locks/Unlocks Range2. You have to do it like this because a custom function cannot chage other cells.
In a code module paste:
VBA Code:
Public R01 As Range, R02 As Range
Public Function LockMe(R1 As Range, R2 As Range) As Boolean
Application.Volatile
Set R01 = R1: Set R02 = R2
LockMe = (Application.WorksheetFunction.CountBlank(R01)) = R01.Rows.Count
End Function
Then, in the worksheet code module paste:
VBA Code:
Private Sub Worksheet_Calculate()
R02.Locked = (Application.WorksheetFunction.CountBlank(R01)) = R01.Rows.Count
End Sub
In any cell on the worksheet enter the formula =LockMe([Range1 Address],[Range2 Address])