It may have been helpful to mention some of those mods spaz
Apache, i have run across three that seem fairly extensive and are currently still being updated, etc. thus far.
I tend to like mods that make the game longer, are well balanced and thought out, and more complicated (Love love love love humungously huge tech trees).
Thals, which are extremely well balanced imo:
http://forums.civfanatics.com/showthread.php?t=385737#post9692398
Hulgruffs
http://forums.civfanatics.com/showthread.php?t=415689
the thing I like about Hulgruffs is more techs, and more resources that make sense, you have to convert resources you gain on the map and from buildings into other resources via buildings, among other things, some units as well. While this mod dosn't feel quite as polished as Thal's (and the policies don't feel as worthwhile to me as they are in thal's), Its being updated regularly and worked on with tender loving care. Definitely worth playing.
Civilization Nights
http://forums.civfanatics.com/showthread.php?t=399510
I havent played this yet, I like the sound of most of it, except for the tech pyramid which turned me off to it for now (but to all their own

), if i read the thread correctly though, it sounds like he is going to go back to a more traditional tech tree in the next version.
This isnt a complete list as far as I can tell, but these are the mods that I have found that interest me

.
Also, your thread will probably be moved by a moderator.
Edit: keeping half an eye on Spaz's mod, but waiting till it is more complete to jump in.
It seems like 2k taking their bloody time in releasing the SDK is really holding the modders back, so may be useful to go B**** them out in their own forums

lol. after which your thread will probably get deleted or locked or you banned! jk (well not really....)