Well, map load speeds are the biggest thing I've noticed when adding more Ram. When it comes to CoD4, you have more than enough RAM, so adding more may only disappoint you when you don't notice much of an improvement.
On the GPU side of things, I used to have an 8800GT OverClocked and then switched to a GTX 260. I could easily tell a difference in performance right away. To give you some reference, I believe the geforce 250 is a shined up version of a geforce 9800. The geforce 260 used the same tech as the higher end 200 series cards. I almost got a geforce 250 until I read up on it a bit. So make sure you browse the specs of different cards so you'll understand what you're getting. I can't tell you much about the newer cards other than I can't afford them so I haven't done any research on them.
If you're on a tight budget like me, around $200 is all I can usually spend on a single upgrade. For that money, I'm sure you could find a 450 or 460 nvidia card. ATI will have some decent selections for that price aswell but I don't follow them much. I think it's safe to say that your new card may require two "6-pin" power connections so make sure your power supply can handle the demand of your new card.
If you find a card that will work with your PSU, you'll find out later that your CPU will be the component holding you down. then you'll be looking for a new CPU/Mobo. It's a never ending cycle. Good luck with your upgrade. I'm confident that you'll be much happier with a GPU upgrade over a stick of ram.
Hopefully I didn't confuse you on any of the rambling I was doing above. The colored text is about the most useful.