I'm pretty sure Rhye is doing/has already done a mod for Civ 4 (I think he has something to do with Firaxis). I'm sure we'll see other mods, such as the ever popular DyP, and Warhammer mods come around soon as well.
The one thing I worry about, is that with all the complexity you can add to your modding, nobody will ever finish a mod, as they will keep having to add new cool stuff!