The most fun I ever had in a civ game came in the late part of a Civ III game. Let me recount what happened:
I was the Greeks, and was put on an island continent with the Incans. I wasn't able to expand much, so I colonized a small island continent right nextdoor with three cities. The Persians dominated that continent except for my few cities. Predictably, the Persians went to war with me, and while I had every city fortified with at least 5 hoplites, they were no match for the 40+ immortals thrown at me. I lost my cities on the Persian continent, I was very unhappy, but I knew a prolonged war wasn't wise, so I waited, and formulated a crazy idea in my mind. The idea was to get revenge in a grandiose style.
So I waited several centuries, until I had discovered the secrets of combustion, flight, amphibious warfare, and so on. Then I created the largest navy in the world (over 40 ships) and an enormous invasion fleet. I stormed the beaches of Persia in D-Day fashion with massive naval and air bombardments, followed up with Marines, tanks, infantry and artillery. Xerxes was still trying to wrap his mind around steam power, so my mighty forces fought hopelessly outclassed opponents (cavalry, musketmen). The Persians put up a valiant fight, but I liberated the entire continent from Xerxes' backward tyrranical rule, save one city in the corner of the island which I left to Xerxes (totally cut off from the rest of the continent by some of my crack troops).
At this time, the Hittites were giving me a run for my money in tech, but the real tough nut to crack was the Celts - they controlled two ENORMOUS continents all to themselves after eliminating the English and someone else. They were huge, and while behind me in tech, they had the cities and resources to grind me into dust. I had already fought them once when they tried to invade my island (My mighty navy of recently discovered destroyers made short work of their frigates and galleons) and won a stunning victory, but times had changed and I was at a severe disadvantage because my only two sources of oil were two small towns in the northern wastelands of the Celtic continents, surrounded by Celtic towns (and every city was surrounded by railroads).
The Celts decided to have another go at me, and only by airlifting 15+ infantry into each oil town each turn was I able to hold them against the masses of Celtic cavalry moved up the rails and thrown at me each turn. My oil was saved, but the prolonged war and military expenditures saw me start to fall behind in tech. I resolved a peace, but knew I was in a dire situation as far as winning the game through launching a spaceship. The Hittites had a massive treasury (over 50,000 gold) and beat me to the Seti wonder. In order to get an edge on science and hopefully out-research and out-build the Hittites to the spaceship, I decided a quick, surgical war was required. I gathered my navy and an invasion fleet of the island city that held the Seti wonder, and went to war. My long-range bombers pummeled the Hittite capital for several turns, while my invasion forces fought doggedly to take the island city, when the unthinkable happened - the Celts and Egyptians jump into the war against me!
I was bereft of allies, and while I had a powerful military, I was no match for the combined might of the Celts, Egyptians and Hittites. Upon losing my oil towns to the Celts, I knew the game was lost, and while I retreated from that game never to take it up again, I have to say it was the most challenging, unique, and amazing situation I had ever been in - and it was all in the modern era.