The CPU may help, but that depends on if it's the bottleneck or not. If you have, say 2GB of RAM and a couple of 7900GTX's in SLI, then, the CPU would show an improvement. However, if you're running 512MB of RAM and a GeForce 6200, then, you're not really going to notice it where it would count: gaming. Video editing would show an improvement, but not low-level tasks like websurfing, e-mail, and office apps.
Now, it's not something I would entirely discourage, but before you buy that specific part, look at what you're using it for. Games tend to rely more on the graphics card and amount of RAM. If you don't have at least 1GB of RAM and do much gaming at all, I'd strongly encourage putting the money toward that instead. Last year I upgraded my system a little bit: I went from 512MB of RAM to 1GB, a GeForce 6600GT to a GeForce 7900GS, and an Athlon64 3000+ to a 3700+. The RAM upgrade was the most noticeable, and while the 7900GS didn't do that much in smoothing performance at my minimum resolution (1024x768) it's allowing me to game at 1600x1200. I did the CPU upgrade just to buy some more time on my main box, and that's been the least impressive upgrade in terms of performance for what I do.