Both Okami (my favorite game EVER) and Muramasa have amazing art style with amazing color palletes that wow me no matter how many times I see them.
Okami:



Muramasa:



Vanillaware also made 2 other impressive-looking titles, GrimGrimoire and Odin Sphere (the latter of which I am currently playing through and is fantastic).
GrimGrimoire:

Odin Sphere:

And as jwk5 said, Shadow of the Colossus is also amazing:


Also, when you think about it, the Halo games also have impressive palletes:

I recently started playing Golden Sun and I am loving every bit of it:

Pretty much any Zelda game can exhibit a lo of color:


The Saboteur recently caught my eye, with it's amazing black and white to color style:


Some other awesome palettes are:
World of Goo:

LostWinds 2:

La-Mulana WiiWare:

Uncharted 2:

Swords and Soldiers:
Flower:

Modern Warfare 2:

Castle Crashers:

Portal:

Patapon:

LittleBigPlanet:

Night Game:

Braid: Aquaria:

And, of course, Chrono Trigger:

Thre's TONS of others, but you get the picture of what I like. Gernerally colorful, but also dark an dreary (SotC). I don't mind games that are mostly without lots of color and such (Red Faction, Half-Life, Modern Warfare 1, Back, Metal Gear Solid 1 and 2, etc.), in fact many of those I love.