Cell Foam vs. Traditional Fiberglass Insulation for Party Wall
While closed cell foam will perform a bit better than fiberglass in noise reduction, there are much better ways to tackle this for the same or lower cost. What you really need for sound isolation is to add mass between the living units and to mechanically separate them. 

If both living areas are sharing a wall, building with double studs that are staggered will prevent sound from traveling through the studs from one dwelling to the next. Another great option for soundproofing is to use double layers of 1/2 inch drywall or a single layer of 5/8 inch drywall on each side (or at least on your side) instead of a single layer of 1/2 inch (typical). 

I've used 5/8 inch drywall everywhere in my home that I've remodeled and even without insulation in the interior walls, it's helped tremendously. I'd skip the cost of cell foam for interior walls and only use it where needed for air sealing and insulation from heat and cold.