There are three official "Alerts" in Star Trek: Red, Yellow, and Blue. There are two additional "Operational Modes", Green and Grey. Finally, there's "Double Red Alert" which happens exactly once in TOS.
"Condition Green" is standard operating condition on a starship, with power distribution and generation on full, non-critical activities allowed, with requests for additional resources routed through Ops. Most episodes of Star Trek spend most of their time in "Condition Green" and it's not really seen on screen. The closest you will get is in Star Trek 3, on the bridge of the Excelsior, the light for "Situation Normal" is green.
The two battle conditions escalate in preparedness.
Yellow Alert automatically energizes the shields but not the weapons systems. It brings all tactical sensors online, and alerts the current duty shift of a dangerous situation. Yellow Alert can be left in place over long periods of time. For example, when investigating an asteroid belt or the like. Unfortunately I don't have any canon references on hand.
Red Alert automatically energizes the shields and weapons systems, bringing all tactical sensors online, and alerts all shifts to go to battlestations (even people who may be currently sleeping, an interesting thing to think about when you see a ship casually throw itself into Red Alert). Impulse engines go to full power generation as well as the warp core; Structural Integrity and Inertial Control fields go to full power. Transporter rooms are fully staffed. Basically, everything on the ship is turned on except for non-critical power drains, and everyone is awakened and put into a duty situation.
(As a personal side note, I often joke that there's no point in Star Trek video games to ever operate in anything but Red Alert. You're often going into a battle situation even if the story acts like you are not going to, and the extra power generation is always helpful. In "real life", though, you have to be concerned with fuel consumption, crew fatigue (you just woke everyone up to run to fire control or damage control or whatever), and "cry wolf" effects on your crew (you want them at peak alertness, not weary from hours of Red Alert with nothing happening).)
The other side effects including the above for these alert modes can be found in the Star Trek TNG Technical Manual. Some of the "automatic" features of the alert levels are features starting with the Galaxy Class; for example, Kirk orders Yellow Alert when the Reliant approaches in Star Trek 2, yet the shields are down. In TNG, the shields would have gone straight up unless countermanded by an order. "Yellow Alert, Mr. Worf, but keep the shields down."
"Blue Alert" or "Condition Blue" is used for any non-standard mode on a starship, and the exact effects of "Condition Blue" depend on that starship. For example, The USS Defiant's cloaking device activated "Condition Blue" on the Defiant, letting the crew know to keep electromagnetic emissions to a limited level, reducing various power modes to the engines, etc. According to the DS9 Invasion! novel, the Defiant could pipe it's waste heat into internal heat sinks to keep it's profile down. On the other hand, Voyager's "Condition Blue" involved landing cycles, as the ship was intended to land on planets as required.
Note that some things which may be considered "Condition Blue" never show up as such; saucer separation in TNG and the Multi-Vector Assault Mode in Voyager's Prometheus class could be considered "Condition Blue" modes, except they usually happened while already at Red Alert.
Double Red Alert
Ironically, there are two stories in TOS where "Double Red Alert" existed; the one story where it may have made sense had "Double Red Alert" edited out, while the one where it was unnecessary had it left in. In "Conscience of the King" Kirk orders a "Double Red Alert" when a phaser set on overload is discovered; the idea is that a "Double Red Alert" is a regular red alert, but immediate, decisive action is required. Frankly, a "Red Alert" with an "Evacuation Order" would have worked better. In the novelization of "Court Martial," the hangup point for the Court Martial was on if Kirk ejected the pod containing an officer BEFORE or AFTER "Double Red Alert" was called. The idea was that a ship going through an Ion Storm would already be at "Red Alert", so they needed something "More Important." The episode was re-written to simply use regular "Red Alert."
Finally, "Condition Grey" is a low power mode that appears in Voyager's Year of Hell. Basically, everything that can be turned off is turned off, and strict recycling is enforced. The condition doesn't refer to specific warning lights but to the lower light levels on the ship involved. One problem I've always had with Voyager's "Condition Grey" is everyone feeding their personal objects into replicators for "Recycling." Replication is very power intensive, and the "breakdown" process isn't any less power intensive than the "construction" process. According to the TNG technical manual, waste is stored in tanks for reconstruction by the replicator, but Voyager implies that the items must be immediately broken down by Replicator, a vast waste of resources. (But then again, Voyager hasn't been known for it's logical application of the Star Trek universe rules).