The first that comes to mind is Desert Combat for Battlefield 1942, Absolutely great modification, almost better than (and in some ways was better than) Battlefield 2. So many great new vehicles, new weapons, and things like the AC-130 gunship were just loads of fun when with a good team.
Forgotten Hope, again for Battlefield 1942, is the second. It takes the more comic-book like Battlefield, injects some more realistic elements, throws in an almost absurd amount of vehicles (literally almost every tank that served appears in that game). It was almost like a whole new game, really, the WWII setting improved in almost every way. It was just a lot of fun. Forgotten Hope 2 was recently released for Battlefield 2, but I have not yet played it (I forgot all about it, actually, until I was writing this up).