I have a code below to only allow a number to be entered into one of 2 cells. I'm trying to do conditional formatting based on these to cells, and the evidence is showing that even when I type a value into either I21 or I22, they are taking on a value of zero. I have conditional formatting stating that if I21>0, then do one thing. And in a separate cell, I have =I22>0, then do another thing. Neither works, and even using =I22<>0, then do formatting, and it doesn't work, telling me that the assumed value is always zero. Any ideas of why, or how to work around this?
Private Sub Worksheet_Change(ByVal Target As Range)
Const pw As String = "password"
If Target.Address <> "$I$21" And Target.Address <> "$I$22" Then Exit Sub
' Locks range if condition is met
If Target.Cells.Count = 0 Then Exit Sub
If (Intersect(Target, Range("$I21")) Is Nothing And Intersect(Target, Range("$I22")) Is Nothing) Then Exit Sub
If Target.Address = "$I$21" Then
If Range("I21").Value > 0 Then
ActiveSheet.Unprotect pw
Range("I22").Select
Selection.Locked = True
ActiveSheet.Protect pw
Else
ActiveSheet.Unprotect pw
Range("I22").Select
Selection.Locked = False
ActiveSheet.Protect pw
End If
Exit Sub
End If
If Target.Address = "$I$22" Then
If Range("I22").Value > 0 Then
ActiveSheet.Unprotect pw
Range("I21").Select
Selection.Locked = True
ActiveSheet.Protect pw
Else
ActiveSheet.Unprotect pw
Range("I21").Select
Selection.Locked = False
ActiveSheet.Protect pw
End If
End If
End Sub
Bookmarks