Oct 10 2021 02:33 AM - edited Oct 10 2021 02:36 AM
Hi Community,
I'm trying to create a second dependent drop down list but the data I want it to be dependent on as multiple data value in a cell which is inserted from a drop down list. I have used VBA code to make it a multi-select drop down so multiple data can be selected in the cell. This can be seen on the attachment under the Technology Area Column, you can select multiple areas. The second column i'm trying to create is under the Portfolio column. I've created the data set under Sheet 2.
The data in the first drop down are technology areas and the second drop down i'm trying to create is the individual technology components within the area. How can I create this?
Thanks
Oct 10 2021 03:48 AM
SolutionOct 10 2021 04:31 AM
@Hans Vogelaar you are a legend. Thank you so much. Can I apply conditional formatting to this?
Oct 10 2021 05:06 AM
Yes, but the implementation depends on what exactly you want to do.
Oct 10 2021 05:10 AM
Oct 10 2021 05:13 AM
So you want different colors within the same cell. That is not possible using conditional formatting, for that is always applied to an entire cell. It would require VBA. It would be complicated, I'll see if I can come up with something. I don't have time for that immediately.
Oct 10 2021 05:14 AM
Oct 10 2021 09:31 AM
Here is a new version.
Oct 16 2021 03:20 AM
Oct 16 2021 04:06 AM
Right-click the sheet tab and select View Code from the context menu to see the code.
The Worksheet_Change event procedure calls the UpdateValue procedure when a the user modifies a cell in B4:B50 or C4:C50, and this in its turn calls the UpdateFormat procedure.
UpdateFormat looks at the source range of the data validation dropdown for the cell, and colors each entry according to its position.
If you want to apply this to more columns, you'd have to expand both the Worksheet_Change and the UpdateFormat procedures.
Oct 10 2021 03:48 AM
Solution