I'm going to assume that your PC is new enough to use PCI Express video cards. If you're not sure, let me know, otherwise you might have a small problem fitting these items into your system.

I wound up getting an XFX card with a ATI RADEON 5830 recently:
http://www.newegg.com/Product/Product.aspx?Item=N82E16814150497. It's handled everything that I've thrown at it without an issue. It runs at right around $220.
If you're looking for something that's a bit cheaper, the NVIDIA GeForce GTS250 offers very solid performance for about $120:
http://www.newegg.com/Product/Product.aspx?Item=N82E16814150479. It's slightly faster version of my previous card, which offers very good performance for newer titles. It still doesn't hold a candle to the one that I listed above, but it's still an awesome deal at a little over half of the price.