Color Coding

[Agnarr]

Hello Everyone!
I desperately need your help!
I need a code to do the following:
sheet "template": column a must always have a numeric value. column b must always have alphabetic value.
sheet "colors": column A and B will have the values as needed. For example 100 is Black, 200 is Blue etc.
Any time I type in column A of "template" a numeric value like 100 column B should write "Black". If I type "Black" in column A then column A must be replaced by "100" and column B should show "Black".
And if I type anything other than what's on the list in the sheet "colors" then no automatic changes should be made. Like i can type the code 888 in column A and manually type "Camo" in column B and the vba should do nothing about it.

Thank you all in advance!

 

[Agnarr]

Oh. I forgot to mention that I should be able to edit any and all values in the sheet "colors" without errors or runtime errors etc...

 

[Agnarr]

Would something similar help?

Private bHandlingChange As Boolean ' Flag to handle recursive changes

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
    If Sh.Name <> "template" Then Exit Sub ' Only process changes in the "template" sheet
    
    If bHandlingChange Then Exit Sub ' Prevent re-triggering
    
    Dim templateSheet As Worksheet
    Dim colorsSheet As Worksheet
    Dim lookupRange As Range
    Dim colorCodes As Range
    Dim colorNames As Range
    Dim changedCell As Range
    Dim foundColor As Range
    Dim searchValue As Variant
    
    On Error Resume Next ' Turn on error handling
    
    ' Attempt to set references to the sheets
    Set templateSheet = ThisWorkbook.Sheets("template")
    Set colorsSheet = ThisWorkbook.Sheets("colors")
    
    On Error GoTo 0 ' Turn off error handling
    
    If templateSheet Is Nothing Or colorsSheet Is Nothing Then
        MsgBox "Please ensure 'template' and 'colors' sheets exist.", vbExclamation
        Exit Sub
    End If
    
    ' Define the ranges to look up in the "colors" sheet
    Set lookupRange = colorsSheet.Range("A:B")
    Set colorCodes = lookupRange.Columns(1)
    Set colorNames = lookupRange.Columns(2)
    
    If Not Intersect(Target, templateSheet.Columns("A")) Is Nothing Then
        Application.ScreenUpdating = False
        
        For Each changedCell In Target
            If changedCell.Column = 1 And changedCell.Value <> "" Then
                searchValue = changedCell.Value
                
                If IsNumeric(searchValue) Then
                    Set foundColor = colorCodes.Find(searchValue, LookIn:=xlValues, lookat:=xlWhole)
                    If Not foundColor Is Nothing Then
                        bHandlingChange = True ' Set flag to handle change
                        changedCell.Value = searchValue
                        changedCell.Offset(0, 1).Value = colorNames.Cells(foundColor.Row, 1).Value
                        bHandlingChange = False ' Reset flag
                    End If
                Else
                    Set foundColor = colorNames.Find(searchValue, LookIn:=xlValues, lookat:=xlWhole)
                    If Not foundColor Is Nothing Then
                        bHandlingChange = True ' Set flag to handle change
                        changedCell.Value = colorCodes.Cells(foundColor.Row, 1).Value
                        changedCell.Offset(0, 1).Value = searchValue
                        bHandlingChange = False ' Reset flag
                    End If
                End If
            End If
        Next changedCell
        
        Application.ScreenUpdating = True
    End If
End Sub