VBA Conditonal Formatting

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

Originally Posted by royUK

Where have you put the code?

in VB?..

I just right clicked the tab and clicked "view code" then copy the code. Is this the right way?

I just figured out I can certainly search "G" and "Y" by using "Format"-"Conditional Formatting" like this:

=ISNUMBER(SEARCH("G",AA4)) then make format color=green
=ISNUMBER(SEARCH("Y",AA4)) then make format color=yellow

But I still need to do the rest three colors by VBA. I'm wondering if there is anyway I can do ALL conditions in VBA? Thanks.