I have been there once and did only dives less than 70 feet. The weather is almost always nice, with air temps 89 F and stiff tropical breezes, little rain (the island looks and feels more like Arizona than the caribbean). The water was high 70s, low 80s (3 mil shorty) and viz about 50 to 80 feet (more like the keys than Caymans).
There is diving of the west side --- dive sites ten minutes away, calm seas, nice reefs (lots of life, plenty of spotted morays, some turtles, rays, etc, lots of nice coral) and decent shallow wrecks (the Antilla, pedernales, star gerren) --- very nice and very easy. Lots of good operators, lots of choices (am, early pm, late pm, night dives). No or little current, surge. Most will either pick you up or operate right of the resort beaches themselves.
There is diving on the south beach area, or SBA --- i didn't go there, but supposedly a little more challenging in terms of depth, current and sea conditions. Longer boat rides, am 2 tank excusions...
The island is very nice overall. But again, I caution --- no one wants to hear the name of Natalie Holloway. And, by the way, consider the restaurant at the Tierra del Sol (great chowder in a bread bowl, ask for Aldo) and the restaurant at the light house (go and watch the sunset there, breath taking and the food is great, but bring yoy credit card!)