Formating Specific Text

[andreabeas]

I need to be able to assign colors to cells depending on what text is inputted.
If a name start with letters between:
A - Cald (I want the cell to be Yellow)
Call - Eg (I want the cell to be Black)
Ek - Hall (I want the cell to be Red)
Etc.....

Can someone please help?

 

[Bernie Deitrick]

Andrea,

Copy the code below, right-click the sheet tab, select "View Code" and paste the code into the
window that appears. You will need to add the "Etc." in the same manner as shown, ans select the
background colors. Note that the second value on one line is the first value on the next -
otherwise, some values will fall in the cracks.

HTH,
Bernie
MS Excel MVP

Private Sub Worksheet_Change(ByVal Target As Range)
Dim myVal As String
If Target.Cells.Count > 1 Then Exit Sub
On Error GoTo NotString
myVal = UCase(Target.Value)

If myVal >= "A" And myVal <= "CALD" Then Target.Interior.ColorIndex = 3
If myVal > "CALD" And myVal <= "EG" Then Target.Interior.ColorIndex = 5
If myVal > "EG" And myVal <= "HALL" Then Target.Interior.ColorIndex = 9
'Etc....

NotString:
End Sub

 

[andreabeas]

This is great! Thank you.
One more question:

I just need it to do it for one column.
How do I select the range?

 

[Bernie Deitrick]

Use after the Target.Cells.Count line:

'To apply the code to column A
If Target.Column <> 1 Then Exit Sub

HTH,
Bernie
MS Excel MVP