Try this in the ThisWorkbook module
The second part of the macro will lock I4 if nothing has been entered in G4 along with taking away the data validation of I4. In order for the cell to truly be locked if G4 is empty, you must password protect the sheets.
Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
Dim rng As Range
Set rng = Target.Parent.Range("I4, G4")
Dim found As Range
Set found = Columns("B").Find(what:=Target, LookIn:=xlValues, LookAt:=xlWhole)
If Target.Count > 1 Then Exit Sub
If Intersect(Target, rng) Is Nothing Then Exit Sub
If Not found Is Nothing Then
Range("C" & found.Row).Value = Range("G4")
End If
If Not Intersect(Target, Range("G4")) Is Nothing Then
With Range("I4")
If Range("G4").Value = 0 Then
.Locked = True
.Validation.InCellDropdown = False
Else
.Locked = False
.Validation.InCellDropdown = True
End If
End With
End If
End Sub
Bookmarks