The vast majority of Oahu snorkeling accommodations are in Waikiki and Honolulu. But there are some other areas to stay, too. On the east shore you can stay in the Kailua/Kaneohe area. You can choose accommodations on the north shore for snorkeling, but you probably only want to do this in the summer. There is a resort area on the west shore as well in Ko Olina/Kapolei.
If you are serious about snorkeling the best spots on Oahu, you will need to rent a car and prepare to do some driving. The snorkeling really is spread around the island, so choosing where to stay based on that is not really an option. You could split your stay, but the island is small enough that this is not really necessary.
Which season you go will help you choose where to stay. Summertime is better for snorkeling the north shore, because winter brings the big surfing waves. Summer is not bad elsewhere on the island, but there can be waves from the south that time of year. The best chances for snorkeling in the winter will be west, south and east shores.
Now where should you stay?
You will find the most accommodation options in the Waikiki area. There isn't much snorkeling around Waikiki, so staying there for a snorkel focused trip will require a rental car and driving around the island. There are seemingly endless options for high-rise hotels in Waikiki. A few that are directly on the beach are: Hilton Hawaiian Village Waikiki Beach Resort, Outrigger Reef Waikiki Beach Resort, Halekulani Hotel, The Royal Hawaiian, Outrigger Waikiki Beach Resort, and Moana Surfrider.
There are bed & breakfasts in Waikiki and condos for rent too. Lastly, there are a lot of options for vacation rentals in Honolulu and Waikiki; check them out on TripAdvisor.
For the east shore of the island, Lanikai Beach in Kailua has some snorkeling. Otherwise, you are driving elsewhere to snorkel. Kailua and Kaneohe have a number of bed & breakfasts available including a B&B hotel on Kaneohe Bay called Paradise Bay Resort. TripAdvisor lists numerous vacation rentals in this area too.
We describe five separate snorkel spots on the north shore in our guide. So, if you are going to Oahu with snorkeling being your main activity, go in the summer and stay on the north shore. You will have access to good snorkeling and might not even need to go elsewhere on the island. The north shore has the Turtle Bay Resort on the east end. The resort also has condos that are often rented out as vacation rentals. Hale’iwa has a couple of bed & breakfasts and quite a few vacation rentals, check some out here.
Lastly, Ko Olina Resort area on the southwest corner of the island has four separate man-made coves with decent snorkeling and two more spots a short drive up the coast. But you will probably want to check out the rest of the island too. This area is developing into a popular place to stay with hotel and condo options. The condos are both rented from the resort itself and from individuals as vacation rentals. Here are the resorts currently in the area: Marriott’s Ko Olina Beach Club, Aulani Disney Resort & Spa, Beach Villas at Ko Olina, and the Four Seasons Resort Oahu at Ko Olina.
One last tip: if amenities like internet, swimming pool, fitness center, laundry facilities, on-site spa or restaurant are important to you, be sure you look for those while you are searching for accommodations so you are not surprised when you arrive.