If I understand properly what you're trying to do (and I think I do), then there's good news: you don't need any VBA for this!

Dave Hawley has a great example of how to do this at his Ozgrid site. You can get it from this page:

http://www.ozgrid.com/download/default.htm

Look for the doanload called ChangingCombo2.zip.

But the solution does not meet my requirements, as I can just see the
combos working, not how they are working.

Do you know how to fire a macro when the user clicks on one of the
options in a dropdown in, say column C?

When you say a 'dropdown in Column C', I don't think you're talking about a combo box, are you, but an auto drop  based on data validation. If that's the case, then a macro can't trigger from that drop, only from a real combobox, which has a full range of events (to do what I think you're proposing you'd probably want code in the after update event.)

But what you can do is base your data validation on the value in a field. Here's how. Let's say you have 3 options in the column C validation, which might be:

Red
Blue
Green

And then Red has suboptions you want to appear in Column D of - say -

Light red
Dark red
Metallic red
Crimson

(other colours would have different lists.)

Type the list of 4 different reds into a spare column somewhere and name the range Red. Do the same for the Blue and Green options.

You can now set the data validation for Column D on a custom basis, as something like:

= INDIRECT(C1)

This will read the value in Column C and convert it to a range reference.

I haven't had time to actually do this, so you may need to play around with it a little, but it should give you the basis of a solution.

you are right in thinking that i want an autodrop based on
data->validation. The example you have taken, exactly matches my
problem. But i cud not figure out a sol. I tried indirect(C1). Does
not work. I named the range of red colours as RED, but don't know
where and how to use it.
Can u please work out a solution and send it to me?

You need to enter =INDIRECT(C1) as a custom data validation on your cell. This probably isn't the place for a full explanation of the data validation options and dialogue, but I thing if you Google with Excel Data Validation you'll find a tutorial.