Creating a Dependent Drop Down List from Multiple Data (Multiple Select using VBA) in One Cell

[OzzieKhawaja]

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

[#2 · Accepted solution]

@OzzieKhawaja 

See the attached version.

Please test thoroughly.

[#3]

@Hans Vogelaar you are a legend. Thank you so much. Can I apply conditional formatting to this?

[#4]

@OzzieKhawaja 

Yes, but the implementation depends on what exactly you want to do.

[#5]

Ok so now want I want is that for each technology area that is selected appears in a different colour and then the different portfolio areas appears in the same colour as the technology area.

[#6]

@OzzieKhawaja 

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.

[#7]

Thats fine, no problem. Whenever you can would be great.

[#8]

@OzzieKhawaja 

Here is a new version.

[#9]

Hi Hans, how do use your colour formula to the columns I want to apply them to?

[#10]

@OzzieKhawaja 

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.