Does ROM Manager need S-OFF?
ROM Manager doesn't do anything particularly special to install a custom ROM. It essentially makes a call to the recovery binary that allows it to save instructions for your recovery partition to run the next time it boots, thereby automating the process of flashing whatever zip file you choose. You could do the exact same thing manually.

Therefore, if your device needs to be S-OFF to flash a custom ROM from recovery, it will also need to be S-OFF in order for ROM Manager to flash a ROM. It will not work on an S-ON device since it won't be able to write to the NAND, and you won't even be able to get a custom recovery to stick in the first place, most likely, which it will ultimately need to flash the ROM.