lock cells based on negative value in another cell

**tkinnard** · 09-09-2016, 03:53 PM

I am looking for a VBA / Macro to lock a range of cells ( D101-D192 ) if the calculated value in R1 falls bellow $1.00.

This is what I have so far and it is not working:

Private Sub Worksheet_Change(ByVal Target As Range)
If Range("R1") < 1 Then
ActiveSheet.unprotect
Range("D101:D192").Locked = True
ActiveSheet.Protect
Else
End If
End Sub

The workbook that I have is a simple order form that sums up column E which is referenced in R2 as [ =$R$1-$F$193 ], R1 on the next page then references R2 as [ =SHEETOFFSET(-1,R2) ].

My desire is when the allowance gets down to zero that it locks the column that is used to place the next order. Then they will need to make contact with me for approval for going over.

I'm a firemen that works in the office as a purchasing agent and take orders from the 11 stations for their station needs. I have a little experience with pc's but not enough to get through the codes.