As a general rule, most custom ROM hacks you'll find on PokéCommunity are shitty. However, a lot of great ROM hacks can be found if you just keep looking. Here I've made a small list of ROM hacks that shy away from the fanfic-tier story telling of most games.
If you think there is a hack that should be added to this list, just say it in the thread, I'll see if I can put it in here.

Gen 1:
Pokémon, Team Rocket Edition. join the ranks of ream rocket with either a Koffing, Ekans or Meowth by your side. Ekans is the easiest, since you can spam wrap everything to death (Since that's how wrap works in gen one)
http://www.pokecommunity.com/showthread.php?t=101051

Pokémon Brown: A game set in an entirely new Region called Rijon. Has some spelling errors, but it's not that noticeable. new story line, of course, new moves, new places. it's one of the few fan-regions I can recommend to people.
http://www.rijon.com/brown/

Gen 2:
Pokémon Prism: The sequel to Brown, much like gold was to silver. Set foot in an entirely new area, and travel back to Rijon. However, unlike gen 2, all of the first region is left intact, not gutted to save space. you play as the son of lance. After a mishap in a mine, he meets up with a Larvitar and must collect the badges of Naljo while righting the wrongs in this new region that's hell bent on instant industrialization.

Pokémon Dark future: Pretty normal hack until someone fucking comes from the future and tells you that Team rocket are finding a very strong leader who is capable of taking over the entire planet. To change this dark future, you'll embark on an adventure with your eevee starter, and even train under lance himself.
http://www.pokecommunity.com/showthread.php?t=205244

Gen 3:
Yet Another Fire Red Hack: Really it's exactly what it claims to be. However, it adds in the physical/special split, new Pokémon from other generations, removes trade evolutions from the game, making most of them simply level up evolution. Starters have changed, most gym leaders use new types due to the new Pokémon they have at their disposal.
http://www.pokecommunity.com/showthread.php?t=289571

Fire Red Omega: Makes all Pokémon obtainable, changes the starter, ramps up the difficulty. Not much else to say. It'll have you thinking quite a bit more about strategy than most mainline Pokémon games
http://www.mediafire.com/?evxxa2wa7drkzpn

Fire Red Evolution: A difficulty romhack with many added features such as a physical/special split, new moves and abilities from future generations as well as the Gen IV-VI cross-gen evolutions, bringing the amount of Pokémon to 416.
http://www.pokecommunity.com/showthread.php?t=329967

Emerald 386: Just makes all the Pokémon obtainable through 3rd gen. I personally like playing this on an emulator, since it allows for any Pokémon to be caught without the use of trading.

Eccentric Emerald: Another 386 hack with slightly increased difficulty, also has the physical/special split of the newer games.
http://www.pokecommunity.com/showthread.php?t=301368

Pokémon Flora Sky: A game that gets very polarized opinions. While some people despise it, others revere it. Set in a new region, pokémon from 4th and 5th gen are available to capture and train. Shaymin is the main legendary of this game, though you meet many of the others along the way. The story of a fight between Palkia and the portal that they open, unleashing Giratina.
Has some serious backtracking and sometimes leaves you with no idea where to go. Make sure to have a walkthrough handy.
http://www.pokecommunity.com/showthread.php?t=183562

Pokémon Glazed: A very expansive game with three regions, one of which is a remastered Johto. Has a physical/special split and a variety of Pokémon from the first five generations.
http://www.pokecommunity.com/showthread.php?t=292279

Pokémmo: An MMO version of Fire Red.
pokemmo.eu

Gen 4:
Sacred Gold and Storm Silver: If you've played any of this guy's hacks, you know what to expect. He's made Fire Red Omega and Blaze Black. There's an increased difficulty curve, great Pokémon availability, and even some new items to help with evolutions that just wouldn't be able to happen otherwise (Like glaceon). HMS are powered up to not make them a damn waste, and Pokémon are given new moves to use whilst leveling up.
http://gbatemp.net/threads/pok%C3%A9mon-sacred-gold-storm-silver.327567/

Bloody Platinum: Just another difficulty hack, with some new things put in. Starters are difficulty, Elite 4 are damn near impossible to beat. Male and female Pokémon have different sprites now, a lot of overworld sprites are changed. Some routes hold up to 14 different Pokémon.
http://gbatemp.net/threads/%C2%B8-%E2%80%A2-%C2%A8pokemon-bloody-platinum%C2%A8-%E2%80%A2-%C2%B8.167328/

Platinum Enhanced: Same deal. All Pokémon available, but your starter's a dragon type and you get it in an unconventional way. Gyms all have six Pokémon and are reasonably tougher because of it. Movesets are buffed, and Pokémon normally found in honey trees are now in the wild. No more slathering for hours just to get that combee.
http://www.mediafire.com/?f3c3i62okjn9qds

Flawless Platinum: A newer 493/difficulty romhack, gym leaders have full teams and most trainers have relatively strong Pokémon. It also has revamped/buffed Pokémon, moves and movesets.
http://www.mediafire.com/download/4z4hbyu1p99u7i6

Gen 5:
Blaze Black / Volt White: changes stats, types, moves, abilities, difficulty and such. If you've played some of the other games He's made (Two of which are on this page) you know what to expect. There's nothing really new to the story here. it's just minor tweaks.
http://gbatemp.net/threads/pok%C3%A9mon-blaze-black-pok%C3%A9mon-volt-white.286850/

Blaze Black 2 / Volt White 2: A successor to Blaze Black / Volt White, Similar to the original except that its based on Black 2 and White 2.
http://projectpokemon.org/forums/showthread.php?26326-Pok%E9mon-Blaze-Black-2-amp-Pok%E9mon-Volt-White-2