drop down box of color's (2003)

Hello all,
I have a need to create a drop down box for a column of data. I need five colors, so I think Conditional Formatting would be out.
Is there any way I can achieve a drop down box for five different colours?
Thanks.

Re: drop down box of color's (2003)

You have in fact two separate questions/problems.

You can create a dropdown list with 5 items to choose from as follows:
- In a separate area of the worksheet, or even in another worksheet, enter the 5 items in a range of 5 contiguous cells.
- Select these 5 cells, then click in the name box on the left hand side of the formula bar and type a name, for example Items.
-Select the cells where you want to show the dropdown arrows.
- Select Data | Validation...
- Select List from the Allow dropdown.
- Enter the formula =Items in the List box, using the name that you assigned to the 5 cells.
- If you wish, select the Input Message and Error Alert tabs to enter appropriate messages.
- Click OK.
A dropdown arrow will appear in the active cell if it is within the specified range.

To apply coloring with more than 3 colors, you can use the Worksheet_Change event in the sheet's code module.
- Right-click the sheet tab and select View Code from the popup menu.
- Paste the following code into the module and modify it to suit your needs:
<code>
Private Sub Worksheet_Change(ByVal Target As Range)
Dim oCell As Range
If Not Intersect(Target, Range("A2:A100")) Is Nothing Then
For Each oCell In Intersect(Target, Range("A2:A100")).Cells
Select Case (oCell)
Case "One"
oCell.Interior.ColorIndex = 4
Case "Two"
oCell.Interior.ColorIndex = 3
Case "Three"
oCell.Interior.ColorIndex = 8
Case "Four"
oCell.Interior.ColorIndex = 6
Case "Five"
oCell.Interior.ColorIndex = 7
Case Else
oCell.Interior.ColorIndex = xlColorIndexAutomatic
End Select
Next oCell
End If
End Sub
</code>
If you want to color the text instead of the background, change oCell.Interior.ColorIndex to oCell.Font.ColorIndex throughout the code.