Do not change a cell value when different value appear.

[kalcerro_1]

Hello,
I'm creating a sheet in excel where the cells in a column will have the option to change the value and will change the cell and font color.
I want to extend the code so when the user types a different value than stated, the cell remains with no change, I have added the message box but is not enough.

Private Sub Worksheet_Change(ByVal Target As Range)

ActiveSheet.Unprotect "TTTT"

Dim myRng As Range
Set myRng = Worksheets("3. TechAssesm").Range("g5:g82")

For Each cell In myRng
       
    If cell.Value Like "0" Then
        cell.Interior.ColorIndex = 3
        cell.Font.ColorIndex = 2
        
    ElseIf cell.Value Like "1" Then
        cell.Interior.ColorIndex = 45
        cell.Font.ColorIndex = 1
        
       
    ElseIf cell.Value Like "2" Then
        cell.Interior.ColorIndex = 27
        cell.Font.ColorIndex = 1
        
    ElseIf cell.Value Like "3" Then
         cell.Interior.ColorIndex = 10
        cell.Font.ColorIndex = 2
        
    ElseIf cell.Value Like "4" Then
         cell.Interior.ColorIndex = 5
         cell.Font.ColorIndex = 2
         
    ElseIf cell.Value Like "5" Then
        cell.Interior.ColorIndex = 2
        cell.Font.ColorIndex = 1
        
    ElseIf cell.Value Like "X" Then
        cell.Interior.ColorIndex = 15
        cell.Font.ColorIndex = 1
   
    Else
    MsgBox "This score is not available", vbOKOnly, "Not Valid"
       
   End If
   
Next
ActiveSheet.Protect "TTTT"

End Sub

 

[footoo]

 Else
    MsgBox "This score is not available", vbOKOnly, "Not Valid"
    With Application
         .EnableEvents=False  
         .Undo
         .EnableEvents=True
    End With

Do you want the procedure to be triggered only when there is a change to myRng?
At the moment it gets triggered on any change to the worksheet.

 

[kalcerro_1]

Thank you very much @footoo for your reply. The condition I want is: if somebody types 6 , or an e, or a t, or thank you inside one cell, a message appears and display the message that score is not valid, but I want that cell to return to the original value that is "X" I have tried your code but there is an error 1004 ever time I type a different value.

 Else
    MsgBox "This score is not available", vbOKOnly, "Not Valid"
    With Application
         .EnableEvents=False 
         .Undo
         .EnableEvents=True
    End With

Do you want the procedure to be triggered only when there is a change to myRng?
At the moment it gets triggered on any change to the worksheet

 

[footoo]

• Do you want the macro to run only if there is a change to myRng?
• Within myRng will only one cell be changed at a time, or can multiple cells be changed with one entry?
• If the contents of a cell are deleted, what should happen?
• If a wrong value is entered, do you want "X" shown (with the "X coloring)?

 

[kalcerro_1]

• Do you want the macro to run only if there is a change to myRng? KA: Yes, only when there is a change in myRng.
• Within myRng will only one cell be changed at a time, or can multiple cells be changed with one entry? KA: Only one cell should run at a time.
• If the contents of a cell are deleted, what should happen? KA: Should remain in "X"
• If a wrong value is entered, do you want "X" shown (with the "X coloring)? KA: Yes, "X" should appear with the respective coloring.

Hello @footoo , thank you for the follow up, my answers:

 

[footoo]

Private Sub Worksheet_Change(ByVal Target As Range)
'ActiveSheet.Unprotect "TTTT"
Dim myRng As Range
Set myRng = Intersect(Target, ActiveSheet.Range("g5:g82"))
If Not myRng Is Nothing Then
    If myRng.Cells.Count > 1 Then Exit Sub
    If myRng.Value Like "0" Then
        myRng.Interior.ColorIndex = 3
        myRng.Font.ColorIndex = 26
    ElseIf myRng.Value Like "1" Then
        myRng.Interior.ColorIndex = 45
        myRng.Font.ColorIndex = 1
    ElseIf myRng.Value Like "2" Then
        myRng.Interior.ColorIndex = 27
        myRng.Font.ColorIndex = 1
    ElseIf myRng.Value Like "3" Then
        myRng.Interior.ColorIndex = 10
        myRng.Font.ColorIndex = 2
    ElseIf myRng.Value Like "4" Then
        myRng.Interior.ColorIndex = 5
        myRng.Font.ColorIndex = 2
    ElseIf myRng.Value Like "5" Then
        myRng.Interior.ColorIndex = 2
        myRng.Font.ColorIndex = 1
    ElseIf myRng.Value Like "X" Then
        myRng.Interior.ColorIndex = 15
        myRng.Font.ColorIndex = 1
    Else
        MsgBox "This score is not available", vbOKOnly, "Not Valid"
        Application.EnableEvents = False
        myRng = "X"
        Application.EnableEvents = True
        myRng.Interior.ColorIndex = 15
        myRng.Font.ColorIndex = 1
    End If
End If
'ActiveSheet.Protect "TTTT"
End Sub