No, none of the games rumble, they just cause the pad to rumble
Sorry, couldn't resist it
I do not have COD4 so couldn't tell you, sorry.
I bought Army of Two though and that makes it rumble like mad!
Other games I have that work with the rumble are:
Motorstorm (When patched) Although isn't very well implemented probably because it was put in after the release.
Uncharted: Drakes Fortune
Burnout Paradise
Thats all of have at the moment, quite a new PS3 owner
I am pretty certain that all new releases will support it now, it would be silly not to.