Solved: conditional formatting by color

Excel 2007 let me change the color of an item in a cell. If I state if word in a1 = Tom than the font = red, every time I write Tom, it appears red coloured.
But I have a more complex need. Infact I have in the same cell more words (for example: Tom, Jack, Ann, etc) an I need to color every name of a different color the way I can easily distinguish every name from the one quickly. Is there a way to do so ?

I appreciate any help

sasa

In 2007, you multiple conditions, so just keep adding to them.

You are right, but the multiple formatting option works only if the names are in different cells ( one name, one cell) but I have many names in the same cell (for example a1= jack tom arthur
b1= ralph annie gregory and so on and I need to color each name differently.

With regards


sasa

Then you need VBA to parse it, pick out the names and colour accordingly.

Why not just split it into individual cells?

Can u help me, with VBA code. I am a newbie. so I am not able to this.
About the split way I tried this , but the my names for one cell are a lot so finally I have a lot of splitted columns, and the look of the sheet is
awful and even more difficult is to read it quickly.

Here's some code that works on the active cell

[vba]

Dim aryNames As Variant
Dim i As Long
Dim ci As Long
Dim pos As Long

aryNames = Split(ActiveCell.Value, " ")

For i = LBound(aryNames) To UBound(aryNames)

Select Case aryNames(i)

Case "Alan": ci = 3
Case "Bob": ci = 5
Case "Jim": ci = 6
Case "Joe": ci = 7
Case "Mike": ci = 10
Case Else: ci = 0
End Select

If ci > 0 Then

pos = InStr(ActiveCell.Value, aryNames(i))
If pos > 0 Then

ActiveCell.Characters(pos, Len(aryNames(i))).Font.ColorIndex = ci
End If
End If
Next i
[/vba]

Thank you a lot. It is perfect. I have to work on a long column, can you help me doing this last passage.

sasa

[vba]

Public Sub ProcessData()
Const TEST_COLUMN As String = "A" '<=== change to suit
Dim i As Long, j As Long
Dim iLastRow As Long
Dim aryNames As Variant
Dim ci As Long
Dim pos As Long

With ActiveSheet

iLastRow = .Cells(.Rows.Count, TEST_COLUMN).End(xlUp).Row
For i = 1 To iLastRow 'iLastRow to 1 Step -1

aryNames = Split(.Cells(i, TEST_COLUMN).Value, " ")

For j = LBound(aryNames) To UBound(aryNames)

Select Case aryNames(j)

Case "Alan": ci = 3
Case "Bob": ci = 5
Case "Jim": ci = 6
Case "Joe": ci = 7
Case "Mike": ci = 10
Case Else: ci = 0
End Select

If ci > 0 Then

pos = InStr(.Cells(i, TEST_COLUMN).Value, aryNames(j))
If pos > 0 Then

.Cells(i, TEST_COLUMN).Characters(pos, Len(aryNames(j))).Font.ColorIndex = ci
End If
End If
Next j
Next i

End With

End Sub
[/vba]

Thank you. Your help allow me to spare a lot of time, thanks again.

sasa