Add drop down list to show entire rows based on the cell color in Column C.

[Rick_Stanich]

How do I create a rule for a Drop Down List to show all the rows that have a specific color in a cell from Column C?

[AliGW]

You can't, at least not without VBA.

[Rick_Stanich]

Yea, I should have been more specific.
By Rule, maybe using Conditional Formatting or Data Validation?

[AliGW]

You cannot filter data with either conditional formatting or data validation.

I think you had better attach a sample workbook with a manual mock-up of what you want to achieve. It only needs to have 10-20 rows of realistic, but desensitised data.

[Rick_Stanich]

I have added two worksheets to the Excel Workbook attached (There are links to worksheets that have been removed on worksheet "Probe Builds").
The Sheet "Probe Builds" shows all the builds. In Column C, there will be cells highlighted "Orange", Green" and "Violet".
Then I added two other worksheets to simulate what would be seen by color selected, named "If user selects green" and or "If user selects Violet".
At the top of the workbook are two highlighted merged cells, Green and Violet. If the user selects one of the colors the worksheet would show all related rows by that color.


Image "Untitled1.png" shows a simple filter applied, the side bar showing color selection is similar to what I am thinking except the rows following the colored cell need to be included.
Example, see worksheet "If user selects Green" or worksheet "If user selects Violet". This is what I would like to see.

This is starting to seem more complicated than I imagined.

[AliGW]

Yes, this is going to be complicated if you want this to be done in situ. It'll have to be VBA - shall I move the thread for you?

[Rick_Stanich]

Yes please, I apologize I did not realize the scope of this.

[AliGW]

No worries - the thread has been moved.

[JohnTopley]

Please Login or Register  to view this content.

Place above code in sheet module ("Probe Builds"): right click on tab>"View Code".,Copy/paste code

Please Login or Register  to view this content.

Place above code in standard module


in "Probe Builds" click on B1 or H1 (I unmerged the cells) to invoke code: results in "TEST"

[Rick_Stanich]

Thank you very much.

[AliGW]

If that takes care of your original question, please choose Thread Tools from the menu link above and mark this thread as SOLVED. You can also access the SOLVED tag by editing the opening post and choosing SOLVED from the drop-down to the left of the title box.

Also, if you have not already done so, remember that you can reward anyone who offered you help towards a solution for your issue by clicking the small star icon (* Add Reputation) located in the lower left corner of the post in which the help was given. By doing so you can add to the reputation(s) of each of those who offered help.

[Rick_Stanich]

I haven't closed this yet, although the solution provided works well. (Thanks to all).
I am attempting to make the macros work from a "personal.xlsb" file.
I need to try and keep this workbook a non macro workbook (The IT gods sent a rumble my way.)
I generally attempt these things until the PC starts to smoke, then I run for help.

[JohnTopley]

If you want a non-macro solution then you need to use an alternative to cell colour to identify your selection(s).

[Rick_Stanich]

I want a macro solution but I want the macros to be in the personal.xlsb file.
Our IT has limitations on using "xlsm" files, but allows the personal.xlsb file.

[JohnTopley]

I have no need to use, and therefore experience of, Personal.xlsb: with my VERY limited understanding, the macro "Select_by_color" can just be placed in "Personal.xlsb" BUT the "Worksheet_SelectionChange" code needs some additional code ["Class Module"?] to make it generic - not even sure generic is correct here.

So hopefully, one the VBA gurus will be able to help with this.