Red looks cool.
Red has been globally adopted by humans as the colour of danger. Blood is this colour, as is fire. Blood and fire are not naturally regular sights to the human eye. If you can see blood or fire about the place then in terms of my over-simplified definition of 'nature', something somewhere is going wrong.
The human eye is tuned to a range of frequencies on the electromagnetic spectrum commonly defined as visible light. Red light is low frequency, followed by Orange, Yellow, Green, Blue and Violet which are higher frequencies. There is an argument that the only reason we evolved to see anything redder than green was because of the specific importance to animals of detecting blood and fire. Grass and sky, both important in their own right, have no truck with redness and the disdain is mutual (well it would rather have to be).
Thus, when Mr Riker proposes a foray into the state of 'red alert' we humans instinctively picture blood and fire. And Coca-Cola. And Santa Claus. Thus the concept of Danger springs forth in our minds and the adrenaline begins to flow. For us watching the show back home this serves to involve us in the drama playing out across our phosphor screens (or perhaps upon the large screen of the cinema). For the human crew members of the Enterprise, this helps them cultivate a mindset of panic and fear, and survival instincts kick in, hence the overwhelming urge to charge about the ship with weapons levelled and fingers on the proverbial (and often actual) triggers.
Of course in recent millenia, humankind has clawed its way out of the blundering clutches of biological evolution through natural selection, and we have both redefined the meaning and status of blood and fire (which are now positive signs of progress and prosperity, so the more the better) and adjusted the proliferation and diversity of the colours we see in our everyday lives (red, amongst others, being usurped from its mundane prior roles to be the colour of choice for all kinds of things, from edible underpants to humerous nasal adornments). This is why the ship accompanies the mood lighting effect with a wail from a siren, and in case there are any hardcore thrill-seekers aboard, attempts to set everyone else on edge with a freakishly calm synthesised human voice, for those particularly important red-alerts, eg "The ship will turn into a giant space-bourne blender in approximately eighteen point three seven four oh two seconds, you have a choice between hard vacuum or a prolonged evisceration. Please remain calm. This is a no-smoking starship. No running, petting or diving in the corridors. Thankyou for lis-".
Of course, what with the Enterprise being a kind of childish vision of a heterogenous, cosmopolitan, utopian hell, there are a lot more species besides humans aboard, and a little known fact is how the designers of the ship have sought to impress the concepts of danger, panic and imminent, grisly demise upon these other more exotic denizens, as for many of these species, the colour red reminds them of fresh Joola grubs as mother used to make, the ship's siren sounds remarkably like the gentle whoosing of the autumn wind through the fields of Koala Bear analogues and the parapsychotic ramblings of a subsentient automaton serve to gently lull them to spontaneous, all-orifice, simultaneous ejaculation (a phenomenon they find quite pleasing, if a little tiresome to clean up after). So the inspired engineers set about fitting the ship out with a secondary system whose function is to irradiate the entire ship with as wide a spectrum of electromagnetic radiation as possible, both above and below that which can be perceived by (wo)man, set to pulse and strobe at numerous cycling sub-frequencies, thus flickering and flashing quite randomly relative to one another, whilst the air is bombarded with all frequencies that can be generated by the ships PA system, again, cycling and pulsing randomly and again only above or below that range of frequencies audible to wo(man) - (in common parlance this sonic range is called 'audible light'). Whilst in most cases this serves to send any alien species into a kind of desperate mania, thereby neatly augmenting the actual danger, it does serve to keep them out of the way so that the real heroes aboard (the humen) can go about solving the problem undisturbed from their adrenaline/testosterone high.
This is why the red alert is red.
Kudos to Tato who reminds us that Red is not suggestive of danger in all cultures. He points out, as an example, that in China red means luck. This explains why, in one episode, when Red Alert sounds aboard the NCC-1701, Mr Sulu leaps into the air with a cry of "YEAAAH Suck it down you slags!" and then suddenly stops mid-pelvic-thrust with a confused expression on his face before rapidly returning to his swivel-chair, and attempting to get on with his work as inconspicuously as possible. Yes he does. The episode is called "The beans of crazy" (as yet un-noded). You must have missed it.