Forum Discussion

Eddy_Megens's avatar
Eddy_Megens
Copper Contributor
May 08, 2021
Solved

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

  • 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

14 Replies

    • Eddy_Megens's avatar
      Eddy_Megens
      Copper Contributor
      Thanks but that does not meet my needs. The text in your cell is not free in relation to the chosen cell colour
  • 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.

     

    ā€ƒ

    • Eddy_Megens's avatar
      Eddy_Megens
      Copper Contributor
      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
      • HansVogelaar's avatar
        HansVogelaar
        MVP

        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?

Resources