Hi All,

I'm trying to shade a selected range in this way:


if it's "R", color=red

if it's "O", color=orange

if it's "P", color=purple

if it contains "Y", color=yellow

if it contains "G", color=green

for example, if a cell is "G.1/31/2009", I would like it to be shaded in green.

Here is a code I'm trying to use, but I am new to VBA and has no idea how to search "Y" or "G" and shade them. Could anyone help me on this? Many thanks in advance.


Option Compare Text 'A=a, B=b, ... Z=z
Option Explicit
 
Private Sub Worksheet_Change(ByVal Target As Range)
 
Dim Cell As Range
Dim Rng1 As Range
     
    On Error Resume Next
    Set Rng1 = ActiveSheet.Cells.SpecialCells(xlCellTypeFormulas, 1)
    On Error GoTo 0
    If Rng1 Is Nothing Then
        Set Rng1 = Range(Target.Address)
        Else
        Set Rng1 = Union(Range(Target.Address), Rng1)
    End If
    For Each Cell In Rng1
        Select Case Cell.Value
            Case vbNullString
                Cell.Interior.ColorIndex = xlNone
                Cell.Font.Bold = False
            Case "R"
                Cell.Interior.ColorIndex = 3
                Cell.Font.ColorIndex = 3
            Case "Y"
                Cell.Interior.ColorIndex = 27
                Cell.Font.ColorIndex = 27
            Case "G"
                Cell.Interior.ColorIndex = 4
                Cell.Font.ColorIndex = 4
            Case "P"
                Cell.Interior.ColorIndex = 29
                Cell.Font.ColorIndex = 29
            Case "O"
                Cell.Interior.ColorIndex = 45
                Cell.Font.ColorIndex = 45
            Case Else
                Cell.Interior.ColorIndex = xlNone
                Cell.Font.Bold = False
        End Select
    Next
 
End Sub