Do not change a cell value when different value appear.

kalcerro_1

New Member
Joined
Feb 28, 2020
Messages
24
Office Version
  1. 365
Platform
  1. Windows
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.

VBA Code:
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
 

Some videos you may like

Excel Facts

Who is Mr Spreadsheet?
Author John Walkenbach was Mr Spreadsheet until his retirement in June 2019.

footoo

Well-known Member
Joined
Sep 21, 2016
Messages
3,185
Office Version
  1. 2016
Platform
  1. Windows
VBA Code:
 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.
 
Last edited:

kalcerro_1

New Member
Joined
Feb 28, 2020
Messages
24
Office Version
  1. 365
Platform
  1. Windows
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.
VBA Code:
 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

Well-known Member
Joined
Sep 21, 2016
Messages
3,185
Office Version
  1. 2016
Platform
  1. Windows
• 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

New Member
Joined
Feb 28, 2020
Messages
24
Office Version
  1. 365
Platform
  1. Windows
• 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

Well-known Member
Joined
Sep 21, 2016
Messages
3,185
Office Version
  1. 2016
Platform
  1. Windows
VBA Code:
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
 

Watch MrExcel Video

Forum statistics

Threads
1,123,253
Messages
5,600,544
Members
414,387
Latest member
Vincent88

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top