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