drop down list dependence on other cell

[bobmc]

Hello...

Cells B1 and B2 are validation drop down lists derived from ranges D2:D4 and E2:E4 respectively.

Let's say the only shape available in the color "Green" is "Triangle". If "Green" is selected, I'd like cell B2's dropdown to either default to "Triangle" without any other option available, or have cell B2 display a message to the effect, "Green is only available in Triangle.".

Any ideas?

Thanks

bobmc

Book2
	A	B	C	D	E
1	Color	Green		Colors	Shapes
2	Shape	Square		Blue	Round
3				Red	Square
4				Green	Triangle
Sheet1

 

[bobmc]

Thank you Damon!

Your VB code works perfectly on my mockup with the countries. Now I'll adapt accordingly. You VB'ers are brilliant! I might actually have to learn it!

bobmc

 

[Damon Ostrander]

A note to Aladin,

You're right (as always!), I could have used a combination of VBA event code and worksheet-based methods (such as dynamic named ranges) rather than doing the entire job in VBA. I guess the only part that had to be implemented in VBA is the part that clears the dependent cell.

Also, where I have the code [City].ClearContents could have been code that reformats the cell color or font, or makes the cell blink, etc. The only problem with this is that then extra code must be added to set it back when the user again enters data in the cell by selecting an item from the list.

Damon