Ron,

Try This


Private Sub Worksheet_Change(ByVal Target As Range)

Application.EnableEvents = False
On Error GoTo ws_exit
If Not Intersect(Target, Range("N7:N151")) Is Nothing Then
With Target
Select Case .Value
Case "Red"
.Interior.ColorIndex = 3
.Font.ColorIndex = 1
.Font.Bold = True
Case "Blue"
.Interior.ColorIndex = 5
.Font.ColorIndex = 2
.Font.Bold = True
Case "Green"
.Interior.ColorIndex = 4
.Font.ColorIndex = 1
.Font.Bold = True
Case "Amber"
.Interior.ColorIndex = 6
.Font.ColorIndex = 1
.Font.Bold = True
Case "Complete"
.Interior.ColorIndex = 1
.Font.ColorIndex = 2
.Font.Bold = True
End Select
End With
End If

ws_exit:
Application.EnableEvents = True

End Sub


End Sub

--

HTH

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

"RonS" wrote in message
...
Tom

Is there a way to do this a single cell at a time using the change event,
for multiple columns?

similar to this:

Private Sub Worksheet_Change(ByVal Target As Range)

Dim oCell As Range

For Each oCell In Intersect(Columns("N"), ActiveSheet.UsedRange)
Select Case oCell.Value
Case "Red"
oCell.Interior.ColorIndex = 3
oCell.Font.ColorIndex = 1
oCell.Font.Bold = True
Case "Blue"
oCell.Interior.ColorIndex = 5
oCell.Font.ColorIndex = 2
oCell.Font.Bold = True
Case "Green"
oCell.Interior.ColorIndex = 4
oCell.Font.ColorIndex = 1
oCell.Font.Bold = True
Case "Amber"
oCell.Interior.ColorIndex = 6
oCell.Font.ColorIndex = 1
oCell.Font.Bold = True
Case "Complete"
oCell.Interior.ColorIndex = 1
oCell.Font.ColorIndex = 2
oCell.Font.Bold = True
End Select
Next oCell

End Sub