Nik,

You need event code like so

Private Sub Worksheet_Change(ByVal Target As Range)

Application.EnableEvents = False
On Error GoTo ws_exit
If Not Intersect(Target, Range("A1:A1000")) Is Nothing Then
With Target
Select Case LCase(.Value)
Case "red": .Font.ColorIndex = 3
Case "green": .Font.ColorIndex = 10
'etc.
End Select
End With
End If

ws_exit:
Application.EnableEvents = True
End Sub

which goes in the worksheet code module. Change the range to suit, and add
extra cnditions.

CF has four colurs in practice, as you have the 'no condition' colour. If
you want more than this you need VBA again. This is an example I posted
receently

Private Sub Worksheet_Change(ByVal Target As Range)

Application.EnableEvents = False
On Error GoTo ws_exit
With Target
If .Count = 1 Then
If .Column = 1 Then
Select Case .Value
Case Is = 1
.Interior.ColorIndex = 3 'red
Case Is = 2
.Interior.ColorIndex = 38 'pink
Case Is = 3
.Interior.ColorIndex = 4 'green
Case Is = 4
.Interior.ColorIndex = 6 'yellow
Case Is = 5
.Interior.ColorIndex = 8 'majenta
Case Is = 6
.Interior.ColorIndex = 5 'blue
Case Is = 7
.Interior.ColorIndex = 15 'grey
Case Is = 8
.Interior.ColorIndex = 38 'rose
Case Is = 9
.Interior.ColorIndex = 1 'teal
Case Else 'none of the above numbers
Exit Sub
End Select
End If
End If
End With

ws_exit:
Application.EnableEvents = True

End Sub


--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"Ntisch " wrote in message
...
Hi ,

I apologise in advance for the rudimentary nature of this post but I am
new to Macros.

What I am trying to do is create a macro that changes the colour of a
word's font after it has been typed into a cell. For example when I
type the word red into a cell I would like to be able to run a macro to
turn the entry into the word red (typed in the red font colour).

An associated question. Is it possible to have more than three types of
conditional formatting within a single spreadsheet?

thanks, Nik


---
Message posted from http://www.ExcelForum.com/