One of the video interviews described Civ V as being ever better for modding and customization than IV. But with Steam, how is that possible? Unless I'm misunderstanding something the SDK and DLL modifications will be more difficult, if not entirely impossible. (Due to integration of SteamWorks in the game exe, among other reasons).
Although to answer your original question, Half-Life 2. I mean really. Valve cultivates some amazing modding communities, I don't even know why this is a concern.
I too am installing RoM. I'll post my experience when I'm done.