1

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?

0

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.

| improve this answer | |
  • The most easiest is,,, add list (range of cell) to Drop Down, then apply either different colors to each item or any one color, using Conditional Formatting. – Rajesh S Dec 30 '19 at 6:04
  • @RajeshS "how do I actually colour the items in the dropdown list?" – Máté Juhász Dec 30 '19 at 17:07
  • @ Máté Juhász ,, to apply colors,, put Drop down data in Range,, the apply either color to them or conditional format like Green to OK,, Red to BAD,, Yellow to AVERAGE then use that Range as list to DD,, ! – Rajesh S Jan 1 at 4:35
  • ok, i'll try that, thank you! – user1124569 Jan 1 at 10:09
  • @RajeshS: I've tried your suggestion, but it doesn't work, maybe you could post some screenshots? – Máté Juhász Jan 2 at 21:36
-1

This solves the issue:

Method 1:

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

enter image description here


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.

enter image description here


  • 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.

enter image description here


  • 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.


enter image description here


enter image description here


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.

enter image description here

  • 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.

| improve this answer | |
  • "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?" – Máté Juhász Jan 3 at 11:53
  • @MátéJuhász,, now check the edited part,, I'm sure this is what you and others are looking for,, hope you up vote twice this time ☺ – Rajesh S Jan 5 at 7:50
  • "I want to categorize the items in that list and make the similar ones appear in the same colour." - you're approach applies the same colour for everything. – Máté Juhász Jan 5 at 9:30
  • @MátéJuhász,, this code also can be extended to apply different category,,, putting IF between line 2 & 3 !! – Rajesh S Jan 6 at 3:45

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.