There is a complete spectrum of tone, from indifference to over-the-top histrionics, but most writers avoid the extremes, and we'll consider tone as having essentially three shades: understatement, hyperbole and a middle ground. Hey, let's examine each shade in turn.
Understatement is a casual or light treatment of the subject. The reader is given to believe that the author does not take a subject seriously. This is common in first-person narratives, but not so common in third-person except in comedy.
Often understatement is used to call upon the moral indignation of the reader because the reader knows it's a serious subject and thinks the narrator should think so, too. This form of irony was common in 19th-century writing (Lermontov, Gogol, et cetera). Some of Nabokov's unreliable villain narrators pretend to be indifferent in order to anger the reader. Voltaire's Candide and a lot of Vonnegut seem to be understated treatments of their subjects.
Hyperbole is the opposite of understatement. In hyperbole, some elements are exaggerated for rhetorical effect. A lot of genre fiction employes hyperbole because it's a way of heightening the drama. A lot of mythological writing is hyperbole. Ain't that a surprise? Hyperbole is also commonly used nowadays for comic effect.
Much horror/suspense writing is hyperbolic. Think of Poe, Lovecraft, Ludlum and friends. Hyperbole can be similar to melodrama, but it's not necessarily the same thing. Purple prose is often hyperbole, but not all hyperbole is purple prose.
The middle ground is the style used by most authors, who present an accurate picture of things as they are. Or as they could be (we are after all talking about fiction here, which includes fantasy, sf and vampiric werewolf romance and zombie comedies). Examples are too numerous to list. Tolstoy, though, is often held up as a realistic tonal stylist whose prose stays out of the way of the story.
Bear in mind that the tonal spectrum isn't absolute, either. One man's middle ground is another man's hyperbole, and some folks' idea of "subtle" involves a sledge hammer. The important thing is to match the tone of the piece to the intention behind it. Unless you write about the same topics from the same emotional perspective, you probably use a variety of tones in your prose, whether you know it or not.
Sometimes, the tone is wrong for the piece. When this happens, I call it "tone-deaf writing."
Failures in tone (tone-deafness)
The spectrum of possible tones in a narrative is not a scale of "good-to-bad" writing, and hyperbolic prose is no better or worse than middle ground prose or understatement. It's a matter of taste and the needs of the writer to manipulate her story/characters/plot/et cetera. But there are a couple of ways in which the tone of a piece can be bad writing, by which I mean here unsuccessful writing (or lazy or thoughtless or cliche writing).
Sentimentality is a danger especially in hyperbole, and it is when the author attempts to impose upon the material a greater emotional burden than it can comfortably bear. Which means that simply mentioning a baby doesn't get you a sympatheic character. All young children are not instantly adorable. All pregnant mothers do not deserve our tears. All ghosts are not immediately frightening. "Sentiment" is emotion that is not supported by the actual text.
Inhibition is usually a by-product of going too far with understatement, and is an author's failure to give due emotional weight to his material. Which is to say, if something tragic (or joyous et cetera) happens and there is no reaction to it by the narrator or any character in the story, or if there's an epiphany reached by a character but the character or narrator doesn't reflect at all on it and the writer is essentially backing away from strong emotion in his writing, we have an inhibited narrative.
Inhibition is my particular bete noir. My agent and I have had some 'lively exchanges' where he tells me I'm not giving enough to my reader and I accuse him (sometimes behind his back, poor man) of trying to get me to dumb down the book. What's actually going on is that I sometimes balk when I bring up a subject that makes me uncomfortable and I don't pursue it in the narrative the way I really should. Which is, you know, incredibly lazy writing and I'm working on it. My opinion is that when a writer stumbles against something that makes him uncomfortable, he should poke around, see what nasty thing is bothering him and then drag it into the story. You'll have to ask Davin how I'm doing with that.
Anyway, that's a too-long discussion of narrative tone. Is this something you're aware of in your own writing? Do you tend to lean one way or another on the tonal scale, and does that create problems for you? Am I forgetting to mention something important about tone?
Also, just because, here's a photo of our back yard, taken on Sunday afternoon by Mighty Reader:

Also Also: If you are in Seattle on Wednesday, July 14th, you should go to the new Elliot Bay Books on Capital Hill to hear Jon Clinch read from his new book Kings of the Earth! And if you haven't already read his first book, Finn, you should buy it and read it.