How to change cell color in excel based on text input

[Chini mao]

I want the cell format (meaning color of the cell) to change based upon the text entered. I have used up the conditional formatting. It only allows three formats, and I require more. I am inquiring if this can be made into a formula for a cell to have the color change upon certain text phrases? So if you have any information then please do the reply.

[Erakna]

Try this event code.

Option Compare Text

Private Sub Worksheet_Change(ByVal Target As Range)
Set r = Range("A1:A20")
If Intersect(Target, r) Is Nothing Then
Exit Sub
End If
On Error GoTo Endit
Application.EnableEvents = False
vals = Array("Cat", "Dog", "Gopher", "Hyena", "Ibex", "Lynx", _
"Ocelot", "Skunk", "Tiger", "Yak")
nums = Array(8, 9, 6, 3, 7, 4, 20, 10, 23, 15)
For Each rr In r
icolor = 0
For i = LBound(vals) To UBound(vals)
If rr.Value = vals(i) Then
icolor = nums(i)
End If
Next
If icolor <> 0 Then
rr.Interior.ColorIndex = icolor
End If
Next
Endif:
Application.EnableEvents = True
End Sub

Right-click on the sheet tab and "View Code". Copy/paste to that sheet module.

Edit then Alt + q to return to the Excel window.

[Bontu]

Select A1:A4 and in Conditional Formatting use Formula Is (in Excel 2007: New Rule / based on formula), with =A1=$A$5 and set the formatting you want.