I've only spent a night and an circle-the-island tour on Tahiti itself; it's
probably not your first choice but you can't help landing there.  A day
enroute is probably all you want. The Intercontinental has nice rooms and a
big pool, but it's in the airport's flight path.

Moorea is nice, however, and has the advantage of a short ferry crossing
from Tahiti rather than a plane flight.  My only diving experience there was
with Moorea Fun Dive and it was a good experience, complete with a buxom
topless woman wading off the beach adjacent to the dive shop at the end of
the dive day whose assets were difficult to ignore as we slowly approached
the dock through the maze of a reef at low tide.  Boring coral, like Hawaii
but worse, and sparse fish, but lots of [well fed] blacktips and a few big
lemon sharks break up the monotony.  The Moorea Intercontinental is a
beautiful property, especially the over-water bungalos which are configured
like one-bedroom suites - the bottom rungs of some of the ladders are
broken, though, and it can be difficult to climb out after a snorkel in the
lagoon unless you have sufficient upper-arm strength.  Every Wednesday night
it sponsors the best on-the-beach seafood buffet extravaganza complete with
a mediocre Polynesian dance show that I've ever enjoyed.

I've explored the Moorea Pearl and the Sofitel as well.  The Pearl is a
beautiful property, though it's a steep climb from the outside lobby all the
way down to the waterfront and over-water rooms and their lagoon was a bit
scummy.  I'd prefer the nifty off-the-lagoon rooms that come with cold
fresh-water splash pools instead.  The Sofitel seemed pretty run down and
buggy.  The Sheraton is supposed to be nice - I'll probably give that a try
when I return.

For diving, Rangiroa is probably your best bet, especially if you like
high-speed drift diving through tidal exchanges in the cuts in the reef.
Marine life is plentiful with lots of turtles and Napoleon wrasses and
barracudi and such.  The Kia Ora hotel is pretty, the only 'luxury' hotel on
the atoll when I was there.  They have some rooms with hot tubs right in
front.  Great for sitting in at night viewing the ocean while sipping French
champagne if you're masochistic enough to sit in a hot tub in the tropics,
but there's no privacy as the French don't seem to worry much about public
exposure.  The onsite dive shop (Blue Dolphin) looked very capable and rents
and trains on Draegers and Inspirations if rebreathing is your thing.
Rangiroa is the starting point for the Aggressor so I didn't spent the night
on land, but I did have a very good lunch and dinner at the Kia Ora and
walked around a bit.  They have a very good bar.

If you're not staying in one of the big-box hotels, beware of the lack of
A/C.  The French don't seem to worry much about staying sweaty and sticky
all the time since the omnipresent cigarette smoke partially nullifies the
resulting odors.  Bugs are also problematic, especially on Moorea where some
sort of repellent is a must.  I've never been bitten as bad as I was at the
outside bar at the Intercontinental at sunset, but it was the summer rainy
season after all.  The inter-island flights all have 44lb weight limits
IIRC.

Why two islands?  For ONE week, you can see and dive in FIVE
different islands -- Moorea, Raiatea, Huahine, Bora Bora, and
either Papeete or another island -- if you take a Tahitian
Princess cruise.

For TWO weeks, you can take one of the 10-day cruises and dive
in more exotic sites and locations such as those on the
Marquesas itinerary I'll be diving in December, and still have
days left over for staying on some island.  We spent our 40th
anniversary on this Marquesas Tahitian Princess cruise earlier
this year.

EXOTIC variety!  What more can you ask for?  :-)  

-- Bob.