Hi all,
I wanted to change a set of cells with a different color when ever
the contents of cell has a particular alphabet like
Green for "N", Yellow for "L", Orange for "M" and Red for "H". I tired
Conditional Formatting but i see that i can only do it for about three
cases or rules. As i need the fourth one too, is there any ways i can
get it done?.

Thanks in advance.

Regards

Morning,

In the VBA editor, double click on the worksst name (it doesn't go in a VBA
module) and try pasting something like this in:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim Intersection As Range

On Error GoTo ErrorExit

DatabaseWidth =
Sheets("Database").Range("DatabaseStart").CurrentR egion.Columns.Count

Set Intersection = Intersect(Target, Range("ColCategory"))

If Not Intersection Is Nothing Then
If Target.Cells.Count = 1 Then
Select Case Target.Formula
Case Is = "Base Stationery"
Target.Interior.ColorIndex = 34
Target.Font.ColorIndex = 1
Case Is = "Envelopes"
Target.Interior.ColorIndex = 33
Target.Font.ColorIndex = 1
Case Is = "Forms/Admin"
Target.Interior.ColorIndex = 39
Target.Font.ColorIndex = 1
Case Else
Target.Interior.ColorIndex = 2
Target.Font.ColorIndex = 1
End Select
End If
End If

ErrorExit:
Exit Sub

End Sub

In this example, "ColCategory" ins the name of the worksheet range where you
want the changes to apply. I included the syntax for changing both the
background colour and the font colour. Just change the values to reflect the
colour you want.

Here's a colour value lookup table:

Black 1
White 2
Red 3
Bright Green 4
Blue 5
Yellow 6
Pink 7
Turquoise 8
Dark Red 9
Green 10
Dark Blue 11
Dark Yellow 12
Violet 13
Teal 14
Gray-25% 15
Gray 50% 16
Periwinkle 17
Plum (D54) 18
Ivory 19
Light Turquoise (D34) 20
Dark Purple 21
Coral 22
Ocean Blue 23
Ice Blue 24
Dark Blue (D11) 25
Pink (D7) 26
Yellow (D6) 27
Turquoise (D8) 28
Violet (D13) 29
Dark Red (D9) 30
Teal (D14) 31
Blue (D5) 32
Sky Blue 33
Light Turquoise 34
Light Yellow 35
Light Green 36
Pale Blue 37
Rose 38
Lavender 39
Tan 40
Light Blue 41
Aqua 42
Lime 43
Gold 44
Light Orange 45
Orange 46
Blue-Gray 47
Gray-40% 48
Dark Teal 49
Sea Green 50
Dark Green 51
Olive Green 52
Brown 53
Plum 54
Indigo 55
Gray 80% 56

Hope this helps, but come back to me if you need any more assistance!

pete

Thanks Pete that was helpful, in my case i am changing the cells color
based on the result of a formula that comes from another sheet, i.e I
have formula which gives an alphabet result into a named range in
another sheet, The color change is based on result of that from
another named range in another sheet. I have tried the code given by
you but looks like it works fine for manual entries but doesn't work
for formulas Can you please guide me on that I am new to VBA.