There is no direct upgrade support, you must format system partitions.
do the user directories and other custom directories that I have
installed get overwritten during the installation?
If these directories are uncommon or /home - no need, you just need to recreate all the configuration and change uid/gids. Some configuration files from /home might not be compatible with new versions of respective software.
I have some custom software in /usr/local
Depends. If required or compatible libraries are available - no problem.
and have my web pages in
/var/www/html. Do I need to copy these to another server before the
upgrade?
If configuration is default - you can directly copy them, but it also depends on the software you use. example: if your site is running PHP, for RHEL 4.x, typical RH-supported PHP version is 5.1. There are lot of backward-incompatible changes in PHP since 5.1. Your site might not work.
Also will the upgrade, reset all the /etc/init.d files and settings?
It will reset everything - you have to format everything except /home /opt /usr/local (if it's on separate partition). There is no need to do "upgrade" - anyway, there are too many incompatible changes - you have to check everything manually. It's easier to build new system. Much easier.
Run virtual server with Scientific Linux 6.x on your desktop (i.e. use Virtualbox), move files and check if everything works the way you want it. Then migrate the server. If aren't running anything, especially designated for Scientific Linux - take CentOS, it's the same RHEL-source-based and binary compatible, but with better community attention.