Auto change colour of cell when text / letter entered into cell

Hi guys,

Again I found another very helpful bit while trying to update my report.

We had a sheet for staff leave, and I needed more than 3 options for conditional formatting, so I found some VB code and modified it as below.

This gave me multipul options that did multipul colors. When I type the letter the cell turns the corresponding color;

W = Yellow
S = Green
SN = Sky Blue
B = Purple
P = Light purple
A = Dark Yellow
BR = Grey
J = Dark Orange
V = Light blue

-> Right click on worksheet and choose 'view code' - past the below anad modify cell ref's as appropriate

Private Sub Worksheet_Change(ByVal Target As Range)
Dim icolor As Integer

    If Not Intersect(Target, Range("B5:AF286")) Is Nothing Then
        Select Case Target
            Case "W"
                icolor = 6
            Case "S"
                icolor = 4
            Case "SN"
                icolor = 33
            Case "B"
                icolor = 7
            Case "P"
                icolor = 39
            Case "A"
                icolor = 44
            Case "BR"
                icolor = 15
            Case "V"
                icolor = 34
            Case "J"
                icolor = 46
            Case "w"
                icolor = 6
            Case "s"
                icolor = 4
            Case "sn"
                icolor = 33
            Case "b"
                icolor = 7
            Case "p"
                icolor = 39
            Case "a"
                icolor = 44
            Case "br"
                icolor = 15
            Case "v"
                icolor = 34
            Case "j"
                icolor = 46
            Case "W.5"
                icolor = 6
            Case "S"
                icolor = 4
            Case "SN"
                icolor = 33
            Case "B.5"
                icolor = 7
            Case "P.5"
                icolor = 39
            Case "A.5"
                icolor = 44
            Case "BR.5"
                icolor = 15
            Case "V.5"
                icolor = 34
            Case "J.5"
                icolor = 46
            Case "w.5"
                icolor = 6
            Case "s.5"
                icolor = 4
            Case "sn.5"
                icolor = 33
            Case "b.5"
                icolor = 7
            Case "p.5"
                icolor = 39
            Case "a.5"
                icolor = 44
            Case "br.5"
                icolor = 15
            Case "v.5"
                icolor = 34
            Case "j.5"
                icolor = 46
            Case Else
                'Whatever
        End Select
        
        Target.Interior.ColorIndex = icolor
    End If

End Sub

Yay - works a treat

You can shorten by using the Ucase function

More examples of select case here

Private Sub Worksheet_Change(ByVal Target As Range)
Dim icolor As Integer

    If Not Intersect(Target, Range("B5:AF286")) Is Nothing Then
        Select Case UCase(Target)
            Case "W", "W.5"
                icolor = 6
            Case "S", "S.5"
                icolor = 4
            Case "SN", "SN.5"
                icolor = 33
            Case "B", "B.5"
                icolor = 7
            Case "P", "P.5"
                icolor = 39
            Case "A", "A.5"
                icolor = 44
            Case "BR", "BR.5"
                icolor = 15
            Case "V", "V.5"
                icolor = 34
            Case "J", "J.5"
                icolor = 46
            Case Else
                'Whatever
        End Select
        
        Target.Interior.ColorIndex = icolor
    End If

End Sub

VBA Noob