Tiberium Essence ( Moddb.com ) is a complete overhaul that changes the game to be a bit closer to Tiberian Sun in theme. Highly polished.
Tiberian History ( Moddb.com ) on the other hand, is more of a "What if we had units from various C&C games all mashed into one mod" sort of thing. Don't expect thematic consistency, but it's quite fun seeing the old-school stuff reinterpreted in the C&C3 engine. Naturally this means the Scrin are a bit underloved compared to GDI and Nod.
The Forgotten ( Moddb.com ) adds a fourth faction, the Forgotten (mutants), along with a campaign for them.
Fallout ( Moddb.com ) changes the game into a sort of Red Alert/Generals thing but not exactly, replacing the existing factions with NATO, USSR, and Australia, setting the game in an alternate 'what if the cold war went hot' history. Good quality art and sound, only downside is zero voices, so you can't rely upon voice alerts (gotta pay more attention to the minimap).
And for something even more unusual, I'd suggest taking a look at MidEast Crisis 2 ( Moddb.com ), because while yes, at first glance it looks like "another Generals style game" (UN, Israelis, Terrorists), it's not exactly that. Gameplay mechanics are altered, where resources are obtained purely by capturing certain structures and points on the map, no harvesting ore/tiberium. Very polished though, with a surprising amount of good decent voice acting.