color by value

Standard procedure would use a function like the one shown below.

Private Function ValueColor(ByVal Value As Variant) As Long

    Dim Values  As Variant
    Dim Colors  As Variant
    Dim Index   As Integer

    Values = Array(0, 1, 2, 3, 4, 5, 6)
    On Error Resume Next
    Index = WorksheetFunction.Match(Value, Values, 0)
    ' if a Value is entered that doesn't exist:-
    ' return the color specified first in the Colors array
    If Err Then Index = 1
    On Error GoTo 0
    ' read cell colours in VBE's Immedaite pane with:-
    ' ? Selection.Font.Color or ? Selection.Interior.Color
    Colors = Array(vbRed, vbYellow, vbGreen, vbBlue, 1137094, 10498160, 49407)
    ValueColor = Colors(Index - 1)
End Function

Here you have two related arrays, Values and Colors. They have the same number of elements. The relationship betwen value and colour is established by the sequence. The value 0  is rserved to indicate that an invalid value was passed to the function causing the function to return the first value in the Colors array. You can add as many values as you like provided you specify the same number of colours.

The function returns a color number (note that enumerations like vbRed are just named numbers). This is a function call you can try. Install it in the same standard code module as the function, place the curser in it and press F5 (=Run).

Sub GetValueColor()
    MsgBox "Colour for Value 5 = " & ValueColor(5)
End Sub

More useful would be the application in an event procedure that responds to a change event. The sample procedure below must be installed in the code module of a worksheet and the procedure ValueColor must then be in the same module. (Read up on "Standard" and "Worksheet" code modules.)

Private Sub Worksheet_Change(ByVal Target As Range)

    Const TriggerRange  As String = "A2:A10"

    ' skip if more than 1 cell was changed (like paste)
    With Target
        If .CountLarge > 1 Then Exit Sub

        If Not Application.Intersect(Target, Range(TriggerRange)) Is Nothing Then
            .Font.Color = ValueColor(Val(.Value))
            ' .Interior.Color = ValueColor(Val(.Value))
        End If
    End With
End Sub

This procedure would fire when a cell in the specified TriggerRange (A2:A10) was changed and change the font colour to one chosen by its value. It only takes a minor change of code to change the cell's fill instead. In fact, the font and fill colours are often set relative to each other but Excel will, automatically, set the font colour to contrast the fill unless you gave over-riding instructions.