Editable Worksheet_Change Result

[Yasier]

I need your help guys...
I'm trying to create a 2 events Worksheet_Change macro in one sheet
it consist of 2 ranges of conditional events
First conditional range would allow some cell access or no access at all
and the Second conditional range would fill some cells with Text or no text

The problem is, for the first condition I need to fill in more than 1 text reference (more than just "ABCD" as the below macro; e.g. "BCDE", "CDEF", etc. )

And in the second condition, I need the result to be still editable after execution, so that users can still change the text or delete cell contents

here's what I've done so far:

Public BCell As Range
Public CCell As Range
Public vcELL As Boolean

Public Sub Worksheet_Change(ByVal target As Range)

Application.EnableEvents = False
Set BCell = Range("J24")
Set CCell = Range("Y30")

If Not BCell Is Nothing Then
Call isGreat(target)
Else: BCell = False
End If
If Not CCell Is Nothing Then
Call isGood(target)
Else: CCell = False
End If
Application.EnableEvents = True
End Sub
Private Sub isGood(ByVal target As Range)
Application.EnableEvents = False
ActiveSheet.Unprotect Password:="123456789"
If Range("$Y$30").Value = "ABCD" Then
Range("$z$30:$aa$38").Locked = False
Range("$z$30:$aa$38").Interior.ColorIndex = 19
ActiveSheet.Protect Password:="123456789"



Else
Range("$z$30:$aa$38").Locked = True
Range("$z$30:$aa$38").Value = " "
Range("$z$30:$aa$38").Interior.ColorIndex = 15
ActiveSheet.Protect Password:="123456789"

End If
Application.EnableEvents = True
End Sub
Private Sub isGreat(ByVal target As Range)
Application.EnableEvents = False
ActiveSheet.Unprotect Password:="123456789"

If Range("$J$24").Value = "WXYZ" Then
Range("$AA$47").Value = ("ANY TEXT")
Range("$AA$48").Value = "ANY OTHER TEXT"
Range("$AA$47:$AA$48").Locked = False

ActiveSheet.Protect Password:="123456789"

Else
Range("$AA$47").ClearContents

Range("$AA$48").ClearContents
Range("$AA$47:$AA$48").Locked = False
ActiveSheet.Protect Password:="123456789"

End If

Application.EnableEvents = False
ActiveSheet.Protect Password:="123456789"

End Sub
Private Sub Worksheet_SelectionChange(ByVal target As Excel.Range)
'Do nothing if more than one cell is changed or content deleted
If target.Cells.Count > 1 Then
vcELL = False
' Only activate if in area with our special values
ElseIf Intersect(target, Range("J24")) Is Nothing Then
vcELL = False
ElseIf Intersect(target, Range("y30")) Is Nothing Then
vcELL = False
Else

vcELL = True
Set CCell = target
End If
End Sub

 

[Yasier]

I've done it

-----------------------------------------------------

Public BCell As Range
Public CCell As Range
Public VCell As Boolean


Public Sub Worksheet_Change(ByVal target As Range)
If target.Cells.Count > 1 Then Exit Sub

' Only activate if in area with our special values
If Intersect(target, Range("$J$24,$Y$30")) Is Nothing Then Exit Sub

Application.EnableEvents = False
Set BCell = Range("$J$24")
Set CCell = Range("$Y$30")

If Not BCell Is Nothing Then
Call isGreat(target)
Else: BCell = False
End If
If Not CCell Is Nothing Then
Call isNice(target)
Else: CCell = False
End If
Application.EnableEvents = True
End Sub

Private Sub isNice(ByVal target As Range)
Application.EnableEvents = False
ActiveSheet.Unprotect Password:="123456789"

If isGood(target.Value) Then
Range("$Z$30:$AA$38").Locked = False
Range("$Z$30:$AA$38").Interior.ColorIndex = 19
Range("$Z$30").Select
ActiveSheet.Protect Password:="123456789"
Application.EnableEvents = False

Else

Range("$Z$30:$AA$38").Locked = True
Range("$Z$30:$AA$38").Value = " "
Range("$Z$30:$AA$38").Interior.ColorIndex = 15
Range("$Z$30").Select
ActiveSheet.Protect Password:="123456789"
Application.EnableEvents = False
End If
Application.EnableEvents = True
End Sub
Private Sub isGreat(ByVal target As Range)

Application.EnableEvents = False
ActiveSheet.Unprotect Password:="123456789"
If Range("$J$24").Value = "XXXX" Then

Range("$AA$47").Value = "AAAA"
Range("$AA$48").Value = "BBBB"

Range("$AA$47").Select

ActiveSheet.Protect Password:="123456789"
Application.EnableEvents = False
Else

Range("$AA$47").Value = ""
Range("$AA$48").Value = ""
Range("$aa$47").Select
ActiveSheet.Protect Password:="123456789"
Application.EnableEvents = False
End If
Application.EnableEvents = True
ActiveSheet.Protect Password:="123456789"

End Sub
Function isGood(X As String) As Boolean

isGood = False
If X = "ABCD" Then
isGood = True
ElseIf X = "BCDE" Then
isGood = True
ElseIf X = "CDEF" Then
isGood = True


End If
End Function
Private Sub Worksheet_SelectionChange(ByVal target As Excel.Range)
'Do nothing if more than one cell is changed or content deleted
If target.Cells.Count > 1 Then
VCell = False
' Only activate if in area with our special values
ElseIf Intersect(target, Range("$J$24")) Is Nothing Then
VCell = False
ElseIf Intersect(target, Range("$Y$30")) Is Nothing Then
VCell = False
Else

VCell = True
Set CCell = target
Set BCell = target

End If
End Sub