Results 1 to 3 of 3
  1. #1
    2 Star Lounger
    Join Date
    Mar 2003
    Thanked 0 Times in 0 Posts

    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?


  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Thanked 31 Times in 31 Posts

    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:
    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
    If you want to color the text instead of the background, change oCell.Interior.ColorIndex to oCell.Font.ColorIndex throughout the code.

  3. #3
    2 Star Lounger
    Join Date
    Mar 2003
    Thanked 0 Times in 0 Posts

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

    Thanks Hans. The code worked fine.
    I appreciate your help.

    All the best.


Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts