Situations in which it would sound/feel cool for me:
1. Regular conversations first of all (also learning a certain language might introduce an interesting quest or two)
2. Sneaking around enemy basecamps and so on. Usually I'd prefer listening to an interesting scripted conversation, some easter eggs and so on (like the mutant conversations in Fallout 3 http://www.youtube.com/watch?v=D02R-QnXlHw http://www.youtube.com/watch?v=DXZAOtSSRLE&feature=related . I think I remember playing Splinter Cell Chaos Theory and I was crawling down a vent and I overheard a conversation between two guards. One of them mentioned Prince of Persia Warrior Within, the new Ubisoft game back at the time and it was funny cuz Splinter Cell was Ubisoft's game too.
However... just for the sake of it some mobs might just speak something other than English. Would be interesting and it would bring something of its own to the game.
3. Fighting. Hearing enemies cursing you in some other language would be fun. Unless it sounds hilarious and is hard to take seriously, but then it would give birth to a lot of spoof videos and memes and that would be fun as well.
I guess this foreign language could work in some places, I doubt it would work in important moments of the game's plot, but I'm sure they can think of something. I mean going in this kind of direction will make the game quite vivid and more lively and... we barely had accents in Oblivion. In Fallout 3 we had a lot of accents which is good, they might do the same for Skyrim. Now all we need is 'something more'.