Love this question!
Huge SCUBA fan.
My favorite (Warm water) spot so far is really anywhere near Fort Lauderdale Florida. You can charter a boat or just walk off the beach. Crystal clear water and a ton of life all around. I'd have to look at my log book to get any actual site names. I've done the Australia and Puerto Rico but Florida is still the top of my list.
Fave cold water spots Lake Michigan has a ton of wrecks that are fun, but I also love the random quarry dives, Haigh Quarry in Kankakee, IL is where I got my first Cert and there is something to be said about low viz dives that becomes addicting. Adventure at every turn! I have yet to blend my love of kayaking and SCUBA but you may have sparked something!
What are your fave spots?!?