Forum Discussion
Dropdown menu with specified colors and still free text in that colored cell
Hello,
I know how to create a dropdown menu in Excel with text and a color with text but the text can not be changed. What I do not know is how to create a dropdown menu with predefined/specified colors AND still able to fill the cell with text by myself? Please inform me about the specific proces. Regards
Thank you for your email message. It turns out that I had misinterpreted your request. Unfortunately, it's not possible to do what you want using conditional formatting - if a cell doesn't meet the condition, the rule will not be applied. You need VBA code instead.
See the attached workbook. It is a macro-enabled workbook, so you will have to allow macros when you open it.
ā
Code in the worksheet module:
Private Sub Worksheet_Change(ByVal Target As Range) Dim cel As Range Dim rng As Range If Not Intersect(Range("D2:D14"), Target) Is Nothing Then ' Loop through the modified cells in D2:D14 For Each cel In Intersect(Range("D2:D14"), Target) ' Does the value occur in the list of colors? Set rng = Range("A2:A6").Find(What:=cel.Value, LookAt:=xlWhole) If Not rng Is Nothing Then ' User selected a color from the list, so copy the color cel.Interior.Color = rng.Interior.Color Else ' User entered text manually; leave color as it is End If Next cel End If End Sub
14 Replies
- NikolinoDEGold Contributor
- Eddy_MegensCopper ContributorThanks but that does not meet my needs. The text in your cell is not free in relation to the chosen cell colour
Do you mean Data Validation of type List? If so: in the Data Validation dialog, activate the Error Alert tab, and clear the check box 'Show error alert after invalid data is entered', then click OK.
ā
- Eddy_MegensCopper ContributorHello Hans,
thanks for the suggestion but no this is not what I mean. I only want that in a certain cell I can choose some, 5 or 6, different predefined colours ( red, green yellow etc). After choosing for example red the cell is red BUT then I want to possibility to write my text in this red cellYou wrote "I know how to create a dropdown menu in Excel with text and a color with text". Can you explain how you do that?