Thanks for your replies

Here is where my lack of experience shows I don't know how to format
the if statement to select the range


A4:M500 is my range
Based on the value on cell (N) I want the case select to color the row
A thru M a certain color.
























On Sun, 19 Nov 2006 13:44:08 +1100, "macropod"
wrote:

Hi Little Penny,

How do I do this.

Well, you'd have to change that code to suit your needs. For example,
change:

Set Rng1 = ActiveSheet.Cells.SpecialCells(xlCellTypeFormulas, 1)
On Error Goto 0
If Rng1 Is Nothing Then
Set Rng1 = Range(Target.Address)
Else
Set Rng1 = Union(Range(Target.Address), Rng1)
End If
For Each Cell In Rng1

to something like:

If .Row = 5 And .Row <= 10 And .Column = 3 And .Column <= 10 Then

to define the range you want to apply the formatting to (the above defines
the range as C5:J10), and modify the Select case procedure, to something
like:

Select Case Cell.Value
Case vbNullString
Cell.Interior.ColorIndex = xlNone
Case "NOT SHIPPED"
Cell.Interior.ColorIndex = 3
Case "SHIPPED"
Cell.Interior.ColorIndex = 4
Case "IN PROUTE"
Cell.Interior.ColorIndex = 38
Case "AMEX"
Cell.Interior.ColorIndex = 6
' Add more coses as needed, then finish off with the next two lines to
' deal with anything not already catered for.
Case Else
Cell.Interior.ColorIndex = xlNone
End Select

and change the 'Next' to 'End If'.