I broke down and got this the other day, in spite of it being fairly old already. I figured I couldn't go wrong with this one. I really love Civ 2 and 3. Civ 1 was slightly before my time.
Everything about this game is better than Civ 3. They've really outdone themselves. Some changes are obvious, like the fact that it's beautiful. Some changes are less obvious, like the modding support and improved AI.
I tried playing on the warlord setting, which is a difficulty level of about 3 out of 10, and wasn't humiliated, but was pretty much stopped cold. The AI is definitely not a push over any more. The only problem with that is that now I have to think a lot more to be able to actually win, even on the mid levels. I'm not sure I want to think that much while playing a game that lasts several days.
Still, with the apparent ease of fan modifications to the game, it looks like I'll be playing it for some time to come. There are mods out there that essentially change the game into an entirely different game. I've seen anything from Star Wars to fantasy to ultra realistic conversions, with historically accurate unique units for every civ.
All in all, I'm not even slightly disappointed in my purchase, even with having to re-think all of my old strategies. Oooh, I just thought of an interesting rant that I think I'll ad to my blog as soon as I submit this. Stay tuned.