this would allow you to draw one character over another to achieve the look of the screen shot.
Well, thinking of text-mode I somehow find it even more counter-intuitive to draw two different characters at the same position.
The effect in the screenshot uses monochrome characters and each character-position on the screen gets it's own fore- and background color from a separate color table.
With fixed-colored tiles you will have to store each tile in all possible color-combinations. So this doesn't work with a total of 8 or 16 tiles.
I don't think this is a problem, just wanted to point out it's quite different from standard text-mode effects and much more limited.
If I'm understanding this correctly, is this anything like the method used by the Mega Drive to render Sonic the Hedgehog?
First of all they use tiles to construct repeating patterns for the scrolling background layers to save space:

=>

So this is pretty much like a large text-mode screen which can be scrolled left and right.
And the same technique is applied for sprites. This way they can animate only those tiles which actually change (eg. sonic's face keeps constant most of the time).