SOLVED

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

%3CLINGO-SUB%20id%3D%22lingo-sub-2337593%22%20slang%3D%22en-US%22%3EDropdown%20menu%20with%20specified%20colors%20and%20still%20free%20text%20in%20that%20colored%20cell%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2337593%22%20slang%3D%22en-US%22%3E%3CP%3EHello%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20know%20how%20to%20create%20a%20dropdown%20menu%20in%20Excel%20with%20text%20and%20a%20color%20with%20text%20but%20the%20text%20can%20not%20be%20changed.%20What%20I%20do%20not%20know%20is%20how%20to%20create%20a%20dropdown%20menu%20with%20predefined%2Fspecified%20colors%20AND%20still%20able%20to%20fill%20the%20cell%20with%20text%20by%20myself%3F%20Please%20inform%20me%20about%20the%20specific%20proces.%20Regards%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2337593%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EOffice%20365%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2337809%22%20slang%3D%22en-US%22%3ERe%3A%20Dropdown%20menu%20with%20specified%20colors%20and%20still%20free%20text%20in%20that%20colored%20cell%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2337809%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F571650%22%20target%3D%22_blank%22%3E%40Eddy_Megens%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EDo%20you%20mean%20Data%20Validation%20of%20type%20List%3F%20If%20so%3A%20in%20the%20Data%20Validation%20dialog%2C%20activate%20the%20Error%20Alert%20tab%2C%20and%20clear%20the%20check%20box%20'Show%20error%20alert%20after%20invalid%20data%20is%20entered'%2C%20then%20click%20OK.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22S0385.png%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F279288i5D69467427A25EEF%2Fimage-size%2Fmedium%3Fv%3Dv2%26amp%3Bpx%3D400%22%20role%3D%22button%22%20title%3D%22S0385.png%22%20alt%3D%22S0385.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3E%E2%80%83%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2337884%22%20slang%3D%22en-US%22%3ERe%3A%20Dropdown%20menu%20with%20specified%20colors%20and%20still%20free%20text%20in%20that%20colored%20cell%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2337884%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F571650%22%20target%3D%22_blank%22%3E%40Eddy_Megens%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EYou%20wrote%20%22I%20know%20how%20to%20create%20a%20dropdown%20menu%20in%20Excel%20with%20text%20and%20a%20color%20with%20text%22.%20Can%20you%20explain%20how%20you%20do%20that%3F%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2337944%22%20slang%3D%22en-US%22%3ERe%3A%20Dropdown%20menu%20with%20specified%20colors%20and%20still%20free%20text%20in%20that%20colored%20cell%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2337944%22%20slang%3D%22en-US%22%3EHi%20Hans%2C%3CBR%20%2F%3E%3CBR%20%2F%3Ethis%20is%20the%20hyperlink%20to%20the%20instruction%3A%3CBR%20%2F%3E%3CA%20href%3D%22https%3A%2F%2Fwww.exceltip.com%2Ftips%2Fcreate-drop-down-list-in-excel-with-color.html%22%20target%3D%22_blank%22%20rel%3D%22nofollow%20noopener%20noreferrer%22%3Ehttps%3A%2F%2Fwww.exceltip.com%2Ftips%2Fcreate-drop-down-list-in-excel-with-color.html%3C%2FA%3E%3CBR%20%2F%3E%3CBR%20%2F%3ERegards%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2338043%22%20slang%3D%22en-US%22%3ERe%3A%20Dropdown%20menu%20with%20specified%20colors%20and%20still%20free%20text%20in%20that%20colored%20cell%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2338043%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F571650%22%20target%3D%22_blank%22%3E%40Eddy_Megens%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22VIiyi%22%3E%3CSPAN%20class%3D%22JLqJ4b%20ChMk0b%22%3E%3CSPAN%3EHere%20is%20a%20small%20example%20from%20me%2C%20maybe%20this%20will%20help%20you.%3C%2FSPAN%3E%3C%2FSPAN%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ECheers%2C%3C%2FP%3E%3CP%3ENikolino%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2339223%22%20slang%3D%22en-US%22%3ERe%3A%20Dropdown%20menu%20with%20specified%20colors%20and%20still%20free%20text%20in%20that%20colored%20cell%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2339223%22%20slang%3D%22en-US%22%3EThanks%20but%20that%20does%20not%20meet%20my%20needs.%20The%20text%20in%20your%20cell%20is%20not%20free%20in%20relation%20to%20the%20chosen%20cell%20colour%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2339306%22%20slang%3D%22en-US%22%3ERe%3A%20Dropdown%20menu%20with%20specified%20colors%20and%20still%20free%20text%20in%20that%20colored%20cell%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2339306%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F571650%22%20target%3D%22_blank%22%3E%40Eddy_Megens%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EThank%20you%20for%20your%20email%20message.%20It%20turns%20out%20that%20I%20had%20misinterpreted%20your%20request.%20Unfortunately%2C%20it's%20not%20possible%20to%20do%20what%20you%20want%20using%20conditional%20formatting%20-%20if%20a%20cell%20doesn't%20meet%20the%20condition%2C%20the%20rule%20will%20not%20be%20applied.%20You%20need%20VBA%20code%20instead.%3C%2FP%3E%0A%3CP%3ESee%20the%20attached%20workbook.%20It%20is%20a%20macro-enabled%20workbook%2C%20so%20you%20will%20have%20to%20allow%20macros%20when%20you%20open%20it.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22S0391.png%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F279374i6D7319996C80FD1C%2Fimage-size%2Fmedium%3Fv%3Dv2%26amp%3Bpx%3D400%22%20role%3D%22button%22%20title%3D%22S0391.png%22%20alt%3D%22S0391.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3E%E2%80%83%3C%2FP%3E%0A%3CP%3ECode%20in%20the%20worksheet%20module%3A%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-visual%22%3E%3CCODE%3EPrivate%20Sub%20Worksheet_Change(ByVal%20Target%20As%20Range)%0A%20%20%20%20Dim%20cel%20As%20Range%0A%20%20%20%20Dim%20rng%20As%20Range%0A%20%20%20%20If%20Not%20Intersect(Range(%22D2%3AD14%22)%2C%20Target)%20Is%20Nothing%20Then%0A%20%20%20%20%20%20%20%20'%20Loop%20through%20the%20modified%20cells%20in%20D2%3AD14%0A%20%20%20%20%20%20%20%20For%20Each%20cel%20In%20Intersect(Range(%22D2%3AD14%22)%2C%20Target)%0A%20%20%20%20%20%20%20%20%20%20%20%20'%20Does%20the%20value%20occur%20in%20the%20list%20of%20colors%3F%0A%20%20%20%20%20%20%20%20%20%20%20%20Set%20rng%20%3D%20Range(%22A2%3AA6%22).Find(What%3A%3Dcel.Value%2C%20LookAt%3A%3DxlWhole)%0A%20%20%20%20%20%20%20%20%20%20%20%20If%20Not%20rng%20Is%20Nothing%20Then%0A%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20'%20User%20selected%20a%20color%20from%20the%20list%2C%20so%20copy%20the%20color%0A%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20cel.Interior.Color%20%3D%20rng.Interior.Color%0A%20%20%20%20%20%20%20%20%20%20%20%20Else%0A%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20'%20User%20entered%20text%20manually%3B%20leave%20color%20as%20it%20is%0A%20%20%20%20%20%20%20%20%20%20%20%20End%20If%0A%20%20%20%20%20%20%20%20Next%20cel%0A%20%20%20%20End%20If%0AEnd%20Sub%3C%2FCODE%3E%3C%2FPRE%3E%3C%2FLINGO-BODY%3E
Occasional Contributor

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

11 Replies

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

 

S0385.png

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

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

@Eddy_Megens 

That is data validation of type List.

Please follow the instruction in my first reply.

@Eddy_Megens 

 

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

 

Cheers,

Nikolino

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

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

best response confirmed by Eddy_Megens (Occasional Contributor)
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.

 

S0391.png

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
Hello Hans,

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