Well, he is essentially an icon for the Pokemon games. Not only is he the first, but he is the only player character to ever return in latter generations. It's really neat how they designed that in generation II, where the final boss is you from the first game (aka Red). That, along with being the original, is why he is idolized.
I still remember hiking up Mount Silver in G/S not knowing what to expect and encountering that enigmatic guy who only says "..." and upon engaging him in battle, you discover that he is Red, the guy you built up from R/B. And not only that, he has some freakishly powerful, high level pokemon, more powerful than any other trainer you can encounter in any pokemon game. I always thought that was cool, really cool. If that isn't iconic and memorable, I don't know what is. I bet if they did that with other protagonists, they would get a similar reaction Red does.
Plus, Red has some of the best fanart out of all the pokemon player characters. I think that has definitely contributed to the "hype" about him.
Do I think he is overrated? Yeah, a bit. He gets credit for being the original, but as far as being the ultimate trainer goes, other protagonists have accomplished just as much, if not more, than him. Heck, Gold/Lyra/Kris even beats Red at the end of G/S/C/HG/SS, proving that he/she is a better trainer. Lucas/Dawn and Hilda/Hilbert/Rosa/Nate defeated evil organizations much more dangerous than Team Rocket. It is a shame that those protagonists do not get the same credit Red does, but not much you can do about it.
I personally like (almost) all protagonists equally. They all have their individual merits which makes them memorable and should all be appreciated to some extent.