How do I change the font size and color in an Excel Drop Down List?

Question

I was wondering if its possible to Style a Drop Down List in Excel. The text is rather small and has no styling and I was wondering if the drop down list styling could be changed?

What would actually make sense is if the drop down list items copied the same styling as its source cells, i.e. alternating background colour of cells, font, size, etc. Or even the copying of the style of the validation cell itself!?

Is there any way to change this using VBA or any other 3rd party method?

Answer 1

Unfortunately, you can't change the font size or styling in a drop-down list that is created using data validation.

You can style the text in a combo box, however. Follow the instructions here: Excel Data Validation Combo Box

Answer 2

You cannot change the default but there is a codeless workaround.

Select the whole sheet and change the font size on your data to something small, like 10 or 12. When you zoom in to view the data you will find that the drop down box entries are now visible.

To emphasize, the issue is not so much with the size of the font in the drop down, it is the relative size between drop down and data display font sizes.

Answer 3

I work on 60-70% zoom vue and my dropdown are unreadable so I made this simple code to overcome the issue

Note that I selected first all my dropdown lsts (CTRL+mouse click), went on formula tab, clicked "define name" and called them "ProduktSelection"

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

Dim KeyCells As Range
Set KeyCells = Range("ProduktSelection")
    If Not Application.Intersect(KeyCells, Range(Target.Address)) _
           Is Nothing Then

ActiveWindow.Zoom = 100

End If

End Sub

I then have another sub

Private Sub Worksheet_Change(ByVal Target As Range) 

where I come back to 65% when value is changed.