<Directory "/Users/mac_user_shortname/Sites/">
    Options Indexes MultiViews
    AllowOverride None
    Order allow,deny
    Allow from all
</Directory>

<Directory "/Users/user_shortname/Sites/">
    Options Indexes MultiViews
    AllowOverride None
    Order allow,deny
    Allow from all
</Directory>

There is another way to point Apache 2 to my Sites directory after upgrading Tiger to Leopard
1. I used textmate to open this file
/etc/apache2/httpd.conf
2. I commented this line
#DocumentRoot "/Library/WebServer/Documents"
3. Replaced it with this line instead
DocumentRoot "/Users/mac_user_shortname/Sites"
4. Added a file titled mac_user_shortname.conf to :
/etc/apache2/users/
5. Include this snippet into mac_user_shortname.conf

OS X.5: Configure Apache 2 to point at my Sites Directory (ver 2)

Apache