Little Penny, one final touch to shorten the code:
If .Row = 4 And .Row <= 500 And .Column = 14 And .Column <= 14
Then
Can be changed to:
If .Row = 4 And .Row <= 500 And .Column = 14 Then

Mike F
"Little Penny" wrote in message
...
Thanks my final code will look like this

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
Dim Line As Range
On Error Resume Next
With Target
If .Row = 4 And .Row <= 500 And .Column = 14 And .Column <= 14
Then
Set Line = Range(Cells(.Row, 1), Cells(.Row, 13))
Select Case .Value
Case vbNullString
Line.Interior.ColorIndex = xlNone
Case "NOT SHIPPED"
Line.Interior.ColorIndex = 3
Case "SHIPPED"
Line.Interior.ColorIndex = 4
Case "IN PROUTE"
Line.Interior.ColorIndex = 38
Case "AMEX"
Line.Interior.ColorIndex = 6
' Add more cases as needed, then finish off with the next two lines to
' deal with anything not already catered for.
Case Else
Line.Interior.ColorIndex = xlNone
End Select
End If
End With
End Sub


Thanks again macropod


















On Sun, 19 Nov 2006 17:24:02 +1100, "macropod"
wrote:

Hi Little Penny,

Try this:

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
Dim Line As Range
On Error Resume Next
With Target
If .Row = 4 And .Row <= 500 And .Column = 1 And .Column <= 13 Then
Set Line = Range(Cells(.Row, 1), Cells(.Row, 13))
Select Case .Value
Case vbNullString
Line.Interior.ColorIndex = xlNone
Case "NOT SHIPPED"
Line.Interior.ColorIndex = 3
Case "SHIPPED"
Line.Interior.ColorIndex = 4
Case "IN PROUTE"
Line.Interior.ColorIndex = 38
Case "AMEX"
Line.Interior.ColorIndex = 6
' Add more cases as needed, then finish off with the next two lines to
' deal with anything not already catered for.
Case Else
Line.Interior.ColorIndex = xlNone
End Select
End If
End With
End Sub

I'm not clear as to whether you're testing for the presence of the strings
anywhere in A4:M500, or just in a particular column. I've coded for the
former. If it's the latter, change the line:
If .Row = 4 And .Row <= 500 And .Column = 1 And .Column <= 13 Then
to suit. Either way, whatever row is affected will have columns A:M
shaded.
This range is controlled by the line:
Set Line = Range(Cells(.Row, 1), Cells(.Row, 13))

Cheers