Excel - Colouring items in dropdown list

Question

I created a dropdown list on excel but I have lots of items in it. I want to categorize the items in that list and make the similar ones appear in the same colour. All I could find so far was how to colour the cell that contains the drop-down list based on the item I choose (using conditional formatting) but how do I actually colour the items in the dropdown list?

Answer 1

how do I actually colour the items in the dropdown list?

You can not do it with built in drop down.

If it's really a must have then you might develop your own form with such a list, but that point it'd be really complicated.

Answer 2

This solves the issue:

Method 1:

Below is non VBA (Macro) method, can be used with Drop down on Sheet.

---

How it works:

• Create source data in H126:H128.
• Select cell I126.
• Hit Validation from Data TAB.
• Pick H126:H128 as List Source, finish with Ok.

---

• Select cell I126 & from Home TAB hit Conditional Formatting.
• Then New Rule, now select Format only cell contains.
• For Rule, select option Cell value, Equals to & cell $H$126.

---

• Apply an appropriate cell formats & finish with Ok.
• Repeat steps 5 to 8 for other colors.

---

Start clicking the Drop Down you get color effect.

---

Edited:

Method 2:

:Caveat:

Since apply color directly to the Drop down is not possible therefore the only option left is the MACRO (VBA code), by using the Combo box.

This comprised VBA macro and Combo box, the Active X control.

---

---

---

How it works:

• To Open VBA editor, either press Alt+F11 or Right click the Sheet TAB.
• From the Project explorer windows, Double click the ThisWorkbook Icon.
• Copy & Paste this code as standard module.

---

     Private Sub Workbook_Open()

     Application.EnableEvents = False


    With Sheet1.ComboBox1
        .AddItem "Fruits"
        .AddItem "Vegetables"
        .AddItem "Soaps"
        .AddItem "Beverages"
    End With

      Application.EnableEvents = True
    End Sub

N.B. You may put your own item's list (are editable).

---

• From File Menu click Close & Return to Microsoft Excel or hit Alt+Q.

• Now at the Sheet, from Developer TAB hit Design and from left to it, click Insert.

• From ActiveX controls select Combo box Icon and draw on sheet at suitable place.

• Right click the Combo box and from pop up menu hit Properties.

• Set Font color and Link cell property.

N.B. You may set Font color and the Lined cell as you wish (are editable).

• Now Click the Combo box and for it's Change event Copy & Paste this code.

---

    Private Sub ComboBox1_Change()

    Cells.Range("A6").Select
    Cells.Range("A6").Font.ColorIndex = 3       

    '=== below shown Font Bold, Name & Size properties are optional you may skip.

     Selection.Font.Bold = True

     With Selection.Font
        .Name = "Calibri"
        .Size = 11
     End With
     End Sub

---

• Press Alt+Q to Save & return to the Sheet.
• Finally from the Developer TAB Click again the Design button to deactivate it.

N.B.

• Save the Workbook as Macro Enabled (.xlsm).

• Now the Combo box is ready to works.

• Check the Screen Shot above.