Dropdown menu with specified colors and still free text in that colored cell

[Eddy_Megens]

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

[#2]

@Eddy_Megens 

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.

 

[#3]

Hello 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 cell

[#4]

@Eddy_Megens 

You 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?

[#5]

Hi Hans,

this is the hyperlink to the instruction:
https://www.exceltip.com/tips/create-drop-down-list-in-excel-with-color.html

Regards

[#6]

@Eddy_Megens 

That is data validation of type List.

Please follow the instruction in my first reply.

[#7]

@Eddy_Megens 

Here is a small example from me, maybe this will help you.

Cheers,

Nikolino

[#8]

Hello Hans,

thanks and I followed your suggestion but it does not work yet. I have copied the screens to a document to clearify. So if you want to check me please send me your email address. By the way I am living in Holland Regards

[#9]

Thanks but that does not meet my needs. The text in your cell is not free in relation to the chosen cell colour

[#10]

@Eddy_Megens 

Here is Nikolino's workbook modified so that you can enter text freely in the cell with the dropdown.

My email address is hans dot vogelaar at gmail dot com

[#11 · Accepted solution]

@Eddy_Megens 

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

[#12]

Hello Hans,

many many thanks because this is the solution I was searching!!!!