Basically you've been turned into a monster - well all three of you have - and so naturally you decide to go on the rampage, destroying city after city. What else would you do? Naturally the military don't really want to let you do this, so you have soldiers, helicoptors, tanks, and for some reason I could never work out a painter with an exploding pain tin to contend with.
Though Rampage did get repetitive pretty quickly (there are after all only so many ways to bash a building until it falls down), it was a lot of fun because you could play it as cooperatively (or, more commonly, uncooperatively) as you liked. Yes folks, you could bash each other if you got bored of bashing buildings. The thing is, this didn't really matter - as long as one of you was still alive, you all stayed in. In many ways this was similar to Gauntlet, and the seemingly endless number of levels was another thing they had in common.
In terms of childhood memories, Rampage and Gauntlet 2 were among the most fun multiplayer games there were, and this was probably due in part to the game keeping going however you played it. Perhaps there was a certain lack of challenge, but it made for a different type of gaming experience - perhaps fairly akin to today's casual web-based games. In any case, despite its obvious limitations, I retain fond memories of Rampage and it's still surprisingly playable today.