Hello, everyone!
I need some help on a matter (example attached, you might just want to skip to it).
I have a data set that contains some item names and below them the locations, where they are sold (each in a separate cell). So there is this drop-down list with all the items that the user can choose, I want a macro that, when an item is chosen, will search for its name and return the selling locations.
The layout is that all the possible selling locations are listed for the user in black and only the available ones should be colored red. So far I've made a macro that uses the "Range.Find" method to set a predefined range variable to the item's name cell and then offset it to the start of the selling locations. Then through a "until not isempty (range)" loop (with each loop offsetting one down) match each location listed under the item with the complete list and color the corresponding ones in red.
I have two problems:
1. The first one is that some item names are complex, like "Iron / Dark Iron" and that name is contained in a single cell (that's the way it has to be, it cannot be changed) while in the drop-down there are separate entries for "Iron" and "Dark Iron" and each of them must find that one cell. I've gotten somewhere with the "Range.Find" method for simple item names (like "Stove"), but I don't know how to make it work for the complex ones. The "xlPart" won't work, because there are other cells in the range that contain part of the complex names but are not the ones I need.
2. In the list of selling locations for the user each town is broken down into different shops (so for example there are Oklahoma (East), Oklahoma (North) and Oklahoma (whatever)) but in the data table with the item names they are listen only by towns (so, for example only "Oklahoma") and so my current macro only checks off the first "Oklahoma" and continues down - I don't know how to make it check all "Oklahomas".
I've also explained it in the example better, I don't know if it is understandable here.
I haven't included any code, because I thought it would be simpler not to have to mess with my messed-up code. :P
Unfortunately the layout is not subject to change. :/
Whew, thank you very much!
Bookmarks