So I am writing a simple code that essentially conditionally locks a cell whenever the cell next to it is changed to a certain value. When the cell locks, I want to change its value to "Not Applicable". However, when I change one line of code to read cell.value = "Not Applicable", Excel crashes.
Code
Private Sub Worksheet_Change(ByVal Target As Range)
Dim a As Integer
a = ActiveCell.Row
ActiveSheet.Unprotect Password:="secret"
If Range("D" & a) = "x" And (Range("E" & a) = "y" Or Range("E" & a) = "z") Then
Range("F" & a).Locked = False
Range("F" & a).Interior.ColorIndex = 2
ElseIf Range("D" & a) = "w" And (Range("E" & a) = "y" Or Range("E" & a) = "z") Then
Range("G" & a).Locked = False
Range("G" & a).Interior.ColorIndex = 2
Else
'Range("F" & a).value = "Not applicable" <<<<< This one line breaks Excel and causes it to crash. Everything else works fine
Range("F" & a).Locked = True
Range("F" & a).Interior.ColorIndex = 15
Range("G" & a).Locked = True
Range("G" & a).Interior.ColorIndex = 15
End If
ActiveSheet.Protect Password:="secret"
End Sub
The cell that I am trying to change the value of has a dropdown based on another list but that list contains the value "Not applicable" so it should not be an issue as far as I know?
Thank you!