Both the Sheffield stadiums are fantastic old theatres, along with lots mentioned like Villa Park, St James's etc but best stadium I have been to is the Millenium stadium Cardiff. Coming in from the city you dont even see it crammed between the high rises then it just appears magnificent on the river side.
Inside the atmosphere is electric with the top tiers hanging over the bottom tier feels like you are close to the pitch wherever you sit unlike the soulless bowl Wembley that's like watching subuteo from the upper sections.