I can't believe no one is posting Command and Conquer Generals?! It was a total modern RTS game with top down view, although you could scroll down to get closer to the action. Completely modern weapons- nothing futuristic, although some of the stuff about China may
have been exaggerated (like the overlord tank).
Well RA is not that futuristic, but maybe World in Conflict? and if you want something not so modern like WWII i suggest Company of Heroes
World at war is based on the late 80's and early 90's when the Soviet Union was still in existence- that is not exactly modern (but I guess it is not that bad since you can still have some jet fighters and stuff)
I do agree that the first and second Red Alerts were not that futuristic (excluding RA2 expansion pack, though... that was totally a science fiction upgrade or something like that)