Universal apps run Intel code. They have dual binaries (PowerPC and Intel). Intel code is Intel only. Rosetta runs programs that are listed as PowerPC (not Universal).
Anyway, if you do need to run a Rosetta application, there is the possibility of running OS X 10.6 in a virtual machine. I suspect that Parallels, VMWare, and Sun (VirtualBox) may see a small spike in activity upon Lion's release if people find out they have some old PowerPC applications. Hopefully the Lion installation routine will search for PowerPC applications and also see if there are updates available. In many cases, some old drivers or scanning/printing/camera software may be PowerPC.
As to the larger issue, consider that 64-bit Windows drops support for Win16 code (which many programs as late as the Win95/Win98 era still used, at least partially). The rumor is that Windows 8 will be 64-bit only, just like Lion.